Home » SQL & PL/SQL » SQL & PL/SQL » Index 10g (Oracle 10g database)
Index 10g [message #421072] Fri, 04 September 2009 02:23 Go to next message
c.schoeberl
Messages: 22
Registered: August 2009
Junior Member
Hello everybody,

i have a tricky question. First of all i have a table with about 50 Columns. This is our "Main"-Table. In this table are about 20 million entries. To optimize any select on this table, i have an index in the table including some columns. When i make a simple select on this tabel trow an explain plan i got this:

SELECT STATEMENT, GOAL = CHOOSE					
 TABLE ACCESS BY INDEX ROWID					
 INDEX RANGE SCAN


That is ok i mean the costs are very very low and everthing went fine.

to supplement it this is an 9i database.

so now i run this on an 10g database.

SELECT STATEMENT, GOAL = CHOOSE					
 FULL TABLE ACCESS 


I mean this is the same table, the same entrys just only 10g and not 9i. I know the option about an Optimizer hint but in 9i this does not work.

i Hope you understand that it is a big problem if ihave to make an FULL ACCESS on an table with 20 million entrys.

My question is: is there any chance to select over the index ? I mean everthing is the same except the access on the table.
Re: Index 10g [message #421075 is a reply to message #421072] Fri, 04 September 2009 02:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Standard question 1: Have you gathered statistics on the table and it's indexes.
Re: Index 10g [message #421081 is a reply to message #421072] Fri, 04 September 2009 02:52 Go to previous messageGo to next message
c.schoeberl
Messages: 22
Registered: August 2009
Junior Member
No i don't Sad
Re: Index 10g [message #421104 is a reply to message #421072] Fri, 04 September 2009 04:28 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
then you should
Previous Topic: ROWNUM not getting from the table
Next Topic: TX, TM and UL Locks
Goto Forum:
  


Current Time: Thu Sep 29 04:00:05 CDT 2016

Total time taken to generate the page: 0.11210 seconds