Home » RDBMS Server » Performance Tuning » Full Table scan - Query without where clause (11g, 11.2.0.5, AIX)
Full Table scan - Query without where clause [message #589856] Thu, 11 July 2013 08:09 Go to next message
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
Re: Full Table scan - Query without where clause [message #589860 is a reply to message #589856] Thu, 11 July 2013 08:16 Go to previous messageGo to next message
BlackSwan
Messages: 22844
Registered: January 2009
Senior Member
use SEQUENCE datatype instead
Re: Full Table scan - Query without where clause [message #589862 is a reply to message #589860] Thu, 11 July 2013 08:32 Go to previous messageGo to next message
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...
Re: Full Table scan - Query without where clause [message #589863 is a reply to message #589862] Thu, 11 July 2013 08:37 Go to previous messageGo to next message
cookiemonster
Messages: 10989
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.
Re: Full Table scan - Query without where clause [message #589864 is a reply to message #589862] Thu, 11 July 2013 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Full Table scan - Query without where clause [message #589867 is a reply to message #589864] Thu, 11 July 2013 08:45 Go to previous messageGo to next message
BlackSwan
Messages: 22844
Registered: January 2009
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?
Re: Full Table scan - Query without where clause [message #589915 is a reply to message #589867] Fri, 12 July 2013 00:10 Go to previous message
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
Previous Topic: Simple query is taking long time
Next Topic: How to enable AWR in oracle 10g
Goto Forum:
  


Current Time: Wed Oct 01 11:24:43 CDT 2014

Total time taken to generate the page: 0.11491 seconds