RE: Index hints?
There is nothing wrong in having a comma in a hint: SQL> create table test_hint ( c1 number not null , c2 number); Table created. SQL> SQL> create index testix1 on test_hint (c1); Index created. SQL> SQL> set autotrace on explain; SQL> SQL> select * from test_hint a; no rows selected Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 10 TABLE ACCESS (FULL) OF 'TEST_HINT' SQL> SQL> select /*+ index(a,testix1) */ * from test_hint a; no rows selected Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=826 Card=164 Bytes=4 264) 10 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_HINT' (Cost=826 Car d=164 Bytes=4264) 21 INDEX (FULL SCAN) OF 'TESTIX1' (NON-UNIQUE) (Cost=26 Car d=164) Regards, Waleed -Original Message- Sent: Sunday, September 01, 2002 9:28 PM To: Multiple recipients of list ORACLE-L Thanks all the responses. It's the syntax error like Naveen said. Oracle didn't detect the error, it just simply ignore the hints if the syntax is wrong. Thanks again, Chuan -Original Message- Sent: Friday, 30 August 2002 5:33 PM To: Multiple recipients of list ORACLE-L If you specify a hint Oracle is forced to use the index. Your hint's syntax is wrong. USE - /*+ INDEX(test index_a) */ Don't use the comma *** between the table name and the index name Naveen -Original Message- Sent: Friday, August 30, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Hi, All, On a million row table, test, there is primary key on column a. when I run select /*+ index (test,indx_a) */ a, b, c, from test, according to Oracle, I should get the following explain plan. TABLE ACCESS BY INDEX ROWID test 1 INDEX UNIQUE SCAN PK_test 1 But I acctucally got : TABLE ACCESS FULL test 1 The database optimizer mode is choose, and I gathered the statistics on table and index using dbms_utitlity and analyze. In the session level, I also set optimizer_mode=first_rows to push optimizer to choose the index. According to Oracle Doc "use hints to force the optimizer to use the optimal execution plan.", to my understanding, the explain plan under such a situation, should be the first one. Does Oracle follow the hint? or to waht extents, it follows? Appreciated if someone guides me to the right direction. Chuan, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Index hints?
Thanks all the responses. It's the syntax error like Naveen said. Oracle didn't detect the error, it just simply ignore the hints if the syntax is wrong. Thanks again, Chuan -Original Message- Sent: Friday, 30 August 2002 5:33 PM To: Multiple recipients of list ORACLE-L If you specify a hint Oracle is forced to use the index. Your hint's syntax is wrong. USE - /*+ INDEX(test index_a) */ Don't use the comma *** between the table name and the index name Naveen -Original Message- Sent: Friday, August 30, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Hi, All, On a million row table, test, there is primary key on column a. when I run select /*+ index (test,indx_a) */ a, b, c, from test, according to Oracle, I should get the following explain plan. TABLE ACCESS BY INDEX ROWID test 1 INDEX UNIQUE SCAN PK_test 1 But I acctucally got : TABLE ACCESS FULL test 1 The database optimizer mode is choose, and I gathered the statistics on table and index using dbms_utitlity and analyze. In the session level, I also set optimizer_mode=first_rows to push optimizer to choose the index. According to Oracle Doc "use hints to force the optimizer to use the optimal execution plan.", to my understanding, the explain plan under such a situation, should be the first one. Does Oracle follow the hint? or to waht extents, it follows? Appreciated if someone guides me to the right direction. Chuan, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Unless otherwise stated, this e-mail does not represent the views of TransACT Communications Pty Limited. This text and any attachments of this e-mail are confidential and may be legally privileged. This email is for the use of the intended recipient only. If you are not the intended recipient do not take any action in relation to this email, other than to notify TransACT Communications by replying to this e-mail and destroying the original communication. Except as required by law, TransACT Communications does not represent that this transmission is free of errors, viruses or interference.
Re: Index hints?
Naveen, > > If you specify a hint Oracle is forced to use the index. > Not 'forced'. Hence the name 'hint'. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Index hints?
Chuan Zhang, hei£¬you statement choose all rows from the table,Of course oracle will use full table scan! There is no where clause in your SQL.If you add: select a,b,c from test where a=some_value, then of course it will use the index. Regards zhu chao Eachnet DBA 86-21-32174588-667 [EMAIL PROTECTED] === 2002-08-29 23:03:00 ,you wrote£º=== >Hi, All, > > On a million row table, test, there is primary key on column a. > >when I run select /*+ index (test,indx_a) */ a, b, c, from test, according to >Oracle, I should get the following explain plan. >TABLE ACCESS >BY INDEX ROWID test 1 >INDEX >UNIQUE SCAN PK_test 1 > >But I acctucally got : > TABLE ACCESS >FULL test 1 > >The database optimizer mode is choose, and I gathered the statistics on table and >index using dbms_utitlity and analyze. >In the session level, I also set optimizer_mode=first_rows to push optimizer to >choose the index. >According to Oracle Doc "use hints to force the optimizer to use the optimal >execution plan.", to my understanding, the explain plan under such a situation, >should be the first one. > >Does Oracle follow the hint? or to waht extents, it follows? > >Appreciated if someone guides me to the right direction. > >Chuan, = = = = = = = = = = = = = = = = = = = = zhu chao [EMAIL PROTECTED] 2002-08-30 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: zhu chao INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Index hints?
If you specify a hint Oracle is forced to use the index. Your hint's syntax is wrong. USE - /*+ INDEX(test index_a) */ Don't use the comma *** between the table name and the index name Naveen -Original Message- Sent: Friday, August 30, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Hi, All, On a million row table, test, there is primary key on column a. when I run select /*+ index (test,indx_a) */ a, b, c, from test, according to Oracle, I should get the following explain plan. TABLE ACCESS BY INDEX ROWID test 1 INDEX UNIQUE SCAN PK_test 1 But I acctucally got : TABLE ACCESS FULL test 1 The database optimizer mode is choose, and I gathered the statistics on table and index using dbms_utitlity and analyze. In the session level, I also set optimizer_mode=first_rows to push optimizer to choose the index. According to Oracle Doc "use hints to force the optimizer to use the optimal execution plan.", to my understanding, the explain plan under such a situation, should be the first one. Does Oracle follow the hint? or to waht extents, it follows? Appreciated if someone guides me to the right direction. Chuan, -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Naveen Nahata INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
