Home » SQL & PL/SQL » SQL & PL/SQL » How do I recall data from an index?
How do I recall data from an index? [message #36415] Mon, 26 November 2001 15:38 Go to next message
Aarti
Messages: 8
Registered: September 2001
Junior Member
Hi!
To give you an overview, I have set up a table using abstract data types, for points on an axis. I am trying to find the intersection co-ordinates of lines. Each line can have different joints, consisting of x and y values, and this is of type Point for example. This data type is used in a new data type called Pipejoints to store information such as the pipeID, the jointID and the x and y co-ordinates referenced from type Point.
Now I have created an index based on the pipeID, but need to test it by calling the info from it! I don't know how!
Someone please help me!

----------------------------------------------------------------------
Re: How do I recall data from an index? [message #36418 is a reply to message #36415] Mon, 26 November 2001 22:01 Go to previous message
Rob Baillie
Messages: 33
Registered: November 2001
Member
Unfortunately (for you), you can't directly select from an index.

Instead, what you're best looking at doing is testing if the index will be used for your normal SELECT statements.

You can do this by installing the 'Plan table' and then using
SET AUTOTRACE ON EXPLAIN

Once done, performing any select will result in a formatted output giving you Oracle's access path.

If you find that your index isn't used, and you think it should be, then you can try adding hints...

SELECT /*+ INDEX(table_name index_name)*/ ...

Though these are purely hints, not instructions... Oracle may not use them if it does not think them appropraite.

AUTOTRACE (well, explain plan really, though it's a good starting point, and it has the plan table script):
http://otn.oracle.com/doc/server.804/a58246/explan.htm#872

Hints:
http://otn.oracle.com/doc/server.804/a58246/optimiz.htm#2013

----------------------------------------------------------------------
Previous Topic: How to ignore errors and finish the loop?
Next Topic: VERY URGENT
Goto Forum:
  


Current Time: Sun Mar 29 23:35:41 CDT 2020