OraFAQ Forum: Performance Tuning » Full Table scan

Home » RDBMS Server » Performance Tuning » Full Table scan - Query without where clause (11g, 11.2.0.5, AIX)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Full Table scan - Query without where clause [message #589856]

Thu, 11 July 2013 08:09

ishika_20
Messages: 339
Registered: December 2006
Location: delhi

Senior Member

Dear All,

Below query is degrading the performance of database. As we know that, without where clause, query do full table scan.
Now, it is written to generate the sequence no. So I am confused to optimized it. Kindly let me know what steps should we take to optimize it.

SQL> explain plan for
  2  SELECT NVL(MAX(P.NUM_SERIAL_NO), 0) + 1 FROM CNFGTR_IRDA_ENVELOPE_DTLS P
  3  /

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3345343365

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                           |     1 |     4 |  7540   (1)| 00:01:31 |
|   1 |  SORT AGGREGATE    |                           |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| CNFGTR_IRDA_ENVELOPE_DTLS |   300K|  1172K|  7540   (1)| 00:01:31 |
------------------------------------------------------------------------------------------------

9 rows selected.

Index is not created on the column.

Need your kind assistance on this...

Regards,
Ishika

Report message to a moderator

Re: Full Table scan - Query without where clause [message #589860 is a reply to message #589856]

Thu, 11 July 2013 08:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

use SEQUENCE datatype instead

Report message to a moderator

Re: Full Table scan - Query without where clause [message #589862 is a reply to message #589860]

Thu, 11 July 2013 08:32

ishika_20
Messages: 339
Registered: December 2006
Location: delhi

Senior Member

Dear BlackSwan,

Thanks for your response..

Now, we cannot change it to sequence (it's production database). Due to problem at sequence, developer decided to use max.
Any other idea plz...

Report message to a moderator

Re: Full Table scan - Query without where clause [message #589863 is a reply to message #589862]

Thu, 11 July 2013 08:37

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

What happens when two users try to insert into that table at the same time?
They'll both get the same max value, and then one of them will get a primary key violation error.
This is never the correct approach, use a sequence.

Report message to a moderator

Re: Full Table scan - Query without where clause [message #589864 is a reply to message #589862]

Thu, 11 July 2013 08:38

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And developer is stupid unless he thought he has to lock the whole table in exclusive mode which is anyway stupid and he should change to be a MS/Access developer and no more touch to Oracle environment.

Regards
Michel

Report message to a moderator

Re: Full Table scan - Query without where clause [message #589867 is a reply to message #589864]

Thu, 11 July 2013 08:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Now, we cannot change it to sequence (it's production database).
You CAN change to a SEQUENCE; but merely use the excuse that it is Production to decide accept the existing brain dead current implementation.
Which is better alternative; living with what exists now now or doing it right by using SEQUENCE?
Do you have a third alternative?

Report message to a moderator

Re: Full Table scan - Query without where clause [message #589915 is a reply to message #589867]

Fri, 12 July 2013 00:10

ishika_20
Messages: 339
Registered: December 2006
Location: delhi

Senior Member

Thank you All,

I will suggest Developer team to change it to sequence.

Regards,
Ishika

Report message to a moderator

Previous Topic:	Simple query is taking long time
Next Topic:	How to enable AWR in oracle 10g

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 23 15:09:09 CST 2025