Home » SQL & PL/SQL » SQL & PL/SQL » Questions on searching specific rows
Questions on searching specific rows [message #281008] Thu, 15 November 2007 06:53 Go to next message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
I have a table with 20000000 rows and attributes below

SQL> desc table1
Name                       Null?    Type
-------------------------- -------- ----------------------------
ID                         NOT NULL VARCHAR2(15)
NAME                       NOT NULL VARCHAR2(32)
DESC                                VARCHAR2(240)



When I do run a query using the 'NAME' attribute with the command below it finds desired rows very quickly.

SQL> select * from table1 where NAME like 'AAA%':

ID         NAME        DESC
---------- ----------- --------------
307369     AAA233      TEST
257901     AAA574      TEST


However, when I run a query using the 'DESC' attribute with the command below it takes a very long time to get the results when there are only two rows in the entire table with "TEST" in the DESC attribute.

SQL> select * from table1 where DESC='TEST';


Could someone tell me why it takes so long and how I can get the results faster by searching for the rows with the value 'TEST' in the 'DESC' attribute?

Any help will be appreciated.


Oracle version : 9.2.0.7.0
OS : HP-UX B.11.11
CPU : PA8700+ 875MHz x 12
RAM : 24GB

[Updated on: Thu, 15 November 2007 06:56]

Report message to a moderator

Re: Questions on searching specific rows [message #281010 is a reply to message #281008] Thu, 15 November 2007 06:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The most likely is that NAME column (by the way bad name, it is a reserved word) is indexed and DESC one (also a reserved word as well as ID) is not.

Regards
Michel
Re: Questions on searching specific rows [message #281013 is a reply to message #281008] Thu, 15 November 2007 07:01 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

One Silly answer from me ..

Change the name of field DESC to DESCR and Try ..
Smile

Thumbs Up
Rajuvan.
Re: Questions on searching specific rows [message #281019 is a reply to message #281013] Thu, 15 November 2007 07:20 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
The field can't be called desc. Desc is a reserved word (as Michel says) and you cannot create objects with reserved words as identifiers
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production



SQL> create table DESC (X INT);
create table DESC (X INT)
             *
ERROR at line 1:
ORA-00903: invalid table name


SQL> ED
Wrote file afiedt.buf

  1* create table D (DESC INT)
SQL> /
create table D (DESC INT)
                *
ERROR at line 1:
ORA-00904: : invalid identifier
Re: Questions on searching specific rows [message #281033 is a reply to message #281008] Thu, 15 November 2007 07:44 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Quote:

The field can't be called desc. Desc is a reserved word (as Michel says) and you cannot create objects with reserved words as identifiers



SQL> SELECT BANNER FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> CREATE TABLE table1
  2  (ID                          VARCHAR2(15),
  3   NAME                        VARCHAR2(32) not null,
  4  "DESC"                        VARCHAR2(240)
  5  );

Table created.

SQL> DESC TABLE1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 ID                                                 VARCHAR2(15)
 NAME                                      NOT NULL VARCHAR2(32)
 DESC                                               VARCHAR2(240)

SQL> Insert into table1
  2   Values
  3     ('307369', 'AAA233', 'TEST');

1 row created.

SQL> Insert into table1
  2   Values
  3     ('257901     ', 'AAA574', 'TEST');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select * from table1 where NAME like 'AAA%'
  2  ;

ID              NAME       DESC
--------------- ---------- ----------
307369          AAA233     TEST
257901          AAA574     TEST

SQL> select * from table1 where DESC='TEST';
select * from table1 where DESC='TEST'
                           *
ERROR at line 1:
ORA-00936: missing expression


SQL> select * from table1 where "DESC"='TEST';

ID              NAME       DESC
--------------- ---------- ----------
307369          AAA233     TEST
257901          AAA574     TEST

SQL>


Though this is NOT GOOD PRACTICE

Thumbs Up
Rajuvan
Re: Questions on searching specific rows [message #281042 is a reply to message #281033] Thu, 15 November 2007 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a STUPID post.

pablolee of course knows this.
This is newbies forum.
In newbies forum you MUST NOT post bad practices.

Regards
Michel
Re: Questions on searching specific rows [message #281043 is a reply to message #281008] Thu, 15 November 2007 08:11 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@ pablolee/Michel : No offence is meant .

What I wanted You expert to think in that way so that there could some Perfomance related issue IF the table1 is created by this BAD PRACTICE .

Once again No offence is meant ..

Sad
Rajuvan

[Updated on: Thu, 15 November 2007 08:16]

Report message to a moderator

Re: Questions on searching specific rows [message #281045 is a reply to message #281043] Thu, 15 November 2007 09:07 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
I took no offense, however I would point out that the OP posted:
Quote:


select * from table1 where DESC='TEST';


i.e. no quotes. so he's lying in 1 way or another.
As Michel said you really should avoid posting any poor practice techniques in a newbies forum, often the technique is seen but the warning is not (I've just seen on another forum someone take the advice of someone telling them how to change a username - that person has now negated their service agreement with Oracle and Oracle no longer need to support them under this license - They had been told that it was inadviseable to do- by several experts)

I would also suggest that DESC and "DESC" are in fact 2 different names (but that's being picky)
Re: Questions on searching specific rows [message #281055 is a reply to message #281008] Thu, 15 November 2007 09:43 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Yes Point Noted.

Thumbs Up
Rajuvan.
Re: Questions on searching specific rows [message #281144 is a reply to message #281008] Thu, 15 November 2007 18:45 Go to previous messageGo to next message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
Sorry guys.
I changed the attribute names for this post because I thought the real attribute names were long.
The real attribute names are the below:

SQL> desc table1
Name                                      Null?    Type
 ---------------------------------------- -------- ----------------------------
 PPXNMBTYXRWWQOID                          NOT NULL VARCHAR2(15)
 PPXNMBTYXRWWQONAME                        NOT NULL VARCHAR2(32)
 PPXNMBTYXRWWQODESC                                 VARCHAR2(240)

[Updated on: Thu, 15 November 2007 18:46]

Report message to a moderator

Re: Questions on searching specific rows [message #281196 is a reply to message #281144] Fri, 16 November 2007 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not change my first answer:
Michel Cadot wrote on Thu, 15 November 2007 13:56

The most likely is that NAME column [...] is indexed and DESC one [...] is not.

Regards
Michel

[Updated on: Fri, 16 November 2007 02:29]

Report message to a moderator

Re: Questions on searching specific rows [message #281213 is a reply to message #281144] Fri, 16 November 2007 02:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That's a fair point - you've got nasty table names. Shocked
Re: Questions on searching specific rows [message #281216 is a reply to message #281008] Fri, 16 November 2007 02:46 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Maybe you can show us explain_plans for both querys.
Then we can see what oracle probably does to get the desired rows.

Best Regards,
Martijn
Re: Questions on searching specific rows [message #281217 is a reply to message #281008] Fri, 16 November 2007 02:48 Go to previous messageGo to next message
stevefox777
Messages: 26
Registered: September 2006
Junior Member
Thanks guys,

I will create an index and see how it goes.

Cheers
Steve
Re: Questions on searching specific rows [message #281260 is a reply to message #281144] Fri, 16 November 2007 04:33 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
stevefox777

The real attribute names are the below:

SQL> desc table1
Name                                      Null?    Type
 ---------------------------------------- -------- ----------------------------
 PPXNMBTYXRWWQOID                          NOT NULL VARCHAR2(15)
 PPXNMBTYXRWWQONAME                        NOT NULL VARCHAR2(32)
 PPXNMBTYXRWWQODESC                                 VARCHAR2(240)



Gosh! Perhaps you should really name that column "DESC" ... who named those columns? Someone closed his/her eyes and threw keyboard several times against the wall?

Good luck with writing your queries (i.e. typing column names)!
Re: Questions on searching specific rows [message #281263 is a reply to message #281260] Fri, 16 November 2007 04:47 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

Previous Topic: date format
Next Topic: Getting invalid identifier in DBMS_SQL.Parse
Goto Forum:
  


Current Time: Thu Dec 08 12:26:23 CST 2016

Total time taken to generate the page: 0.06756 seconds