You Asked
Hi Tom
I have a table 'LOCS' with 2 columns of varchar datatype (col1 is varchar yet at the moment we have only numeric values stored):
col1 col2
2272 ABC
22722 ABCD
1000 dgdfg
10001 dfm
Now, when I query the table:
select * from locs where contains(LOCS.col1, :searchtext) >0
ORDER BY instr(UPPER(LOCS.col1), :searchtext2);
If i give the searchtext as 1000 the query results as expected:
1000 dgdfg
10001 dfm
But if i give the searchtext as 2272 the query results as otherwise:
22722 ABCD
2272 ABC
Can you please explain?
What should be the correct way of Ordering when I try to find a 'searchtext' and if it is present in both col1 and col2, then 'col1' will take precedence and those rows should come first and within col1 it should be ordered asc.
CREATE TABLE P2PUSER.P2B_LOCATION_SEARCH
( LOC_SEARCH_ID NUMBER(20, 0) NOT NULL PRIMARY KEY
, SALE_UNIT_ID NUMBER(15, 0) NOT NULL
, SALE_UNIT_NAME VARCHAR2(175 BYTE)
, SERIAL_NUMBER VARCHAR2(6 BYTE)
, COMPANY_NAME VARCHAR2(175 BYTE)
, CREATED_ON TIMESTAMP(6)
, UPDATED_ON TIMESTAMP(6)
, STATUS NUMBER(1, 0)
, PROFILE_STATUS NUMBER(1, 0)
, SALE_UNIT_TYPE NUMBER(1, 0)
, GEO_LOCATION "PUBLIC".SDO_GEOMETRY DEFAULT MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(0,0,NULL),NULL,NULL)
, LOCATION_UNIQUE_IDENTIFIER VARCHAR2(225 BYTE));
CREATE INDEX P2PUSER.LOCSEARCH_IDX1 ON P2PUSER.P2B_LOCATION_SEARCH (GEO_LOCATION) INDEXTYPE IS MDSYS.SPATIAL_INDEX PARAMETERS('LAYER_GTYPE=POINT') ;
CREATE INDEX P2PUSER.LOCSEARCH_IDX2 ON P2PUSER.P2B_LOCATION_SEARCH (SERIAL_NUMBER) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('DATASTORE CTXSYS.DEFAULT_DATASTORE WORDLIST LOCSEARCH_WORDLIST STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON COMMIT)') ;
CREATE INDEX P2PUSER.LOCSEARCH_IDX3 ON P2PUSER.P2B_LOCATION_SEARCH (SALE_UNIT_NAME) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('DATASTORE CTXSYS.DEFAULT_DATASTORE LEXER LOCSEARCH_lexer WORDLIST LOCSEARCH_WORDLIST STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON COMMIT)') ;
CREATE INDEX P2PUSER.LOCSEARCH_IDX4 ON P2PUSER.P2B_LOCATION_SEARCH (COMPANY_NAME) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('DATASTORE CTXSYS.DEFAULT_DATASTORE LEXER LOCSEARCH_lexer WORDLIST LOCSEARCH_WORDLIST STOPLIST CTXSYS.EMPTY_STOPLIST SYNC(ON COMMIT)') ;
CREATE INDEX P2PUSER.LOC_IDX5 ON P2PUSER.P2B_LOCATION_SEARCH (LOCATION_UPDATION_TIME ASC) ;
CREATE INDEX P2PUSER.LOC_IDX6 ON P2PUSER.P2B_LOCATION_SEARCH (SALE_UNIT_ID ASC) ;
CREATE INDEX P2PUSER.LS5_IDX ON P2PUSER.P2B_LOCATION_SEARCH (STATUS ASC, PROFILE_STATUS ASC) ;
CREATE UNIQUE INDEX P2PUSER.SYS_C0015130 ON P2PUSER.P2B_LOCATION_SEARCH (LOC_SEARCH_ID ASC) ;
SELECT RES.* FROM
(SELECT RES1.*, rownum rnum FROM
(SELECT
/*+ FIRST_ROWS(25) */
LOCS.SALE_UNIT_ID,
LOCS.SERIAL_NUMBER,
LOCS.SALE_UNIT_NAME,
LOCS.COMPANY_NAME,
LOCS.SALE_UNIT_TYPE,
(CASE
WHEN UPPER(LOCS.SERIAL_NUMBER) LIKE :searchtext1 THEN 2
WHEN UPPER(LOCS.SALE_UNIT_NAME) LIKE :searchtext1 AND UPPER(LOCS.SERIAL_NUMBER) NOT LIKE :searchtext1 THEN 3
WHEN UPPER(LOCS.COMPANY_NAME) LIKE :searchtext1 AND UPPER(LOCS.SERIAL_NUMBER) NOT LIKE :searchtext1 AND UPPER(LOCS.SALE_UNIT_NAME) NOT LIKE :searchtext1 THEN 4
ELSE 5
END) RN1
FROM P2PUSER.P2B_LOCATION_SEARCH LOCS
WHERE (contains(LOCS.serial_number, :searchtext)>0
OR contains(LOCS.sale_unit_name, :searchtext) >0
OR contains(LOCS.company_name, :searchtext) >0 )
AND LOCS.SALE_UNIT_TYPE =1
AND LOCS.STATUS = 1
AND LOCS.PROFILE_STATUS IN (1,2)
ORDER BY RN1,
instr(UPPER(LOCS.SERIAL_NUMBER), :searchtext2),
instr(UPPER(LOCS.SALE_UNIT_NAME), :searchtext2),
instr(UPPER(LOCS.COMPANY_NAME), :searchtext2)
) RES1
WHERE Rownum <= :lastIndex
) RES
WHERE RES.Rnum >= :startIndex;
:searchtext is '1000%'
:searchtext1 is %1000%
:searchtext2 is '1000'
THis is the same query which I had discussed in another question with you was consuming 90% CPU on DB server. Now we have attached a SQL profile to resolve the issue.
But there is still problem with the ordering.
and Chris said...
The issue is instr will return the same value for both rows! So the ordering is non-deterministic. You're just lucky you get the correct ordering first time around.
If the columns always contain numbers you can get the "correct" ordering by applying a to_number():
create table t (
x varchar2(10), y varchar2(10)
);
insert into t values ('2272', 'ABC');
insert into t values ('22722', 'ABC');
create index i on t(x) indextype is ctxsys.context ;
select x, instr(x, '2272') from t
where contains (x, '2272%', 1) > 0
order by to_number(x);
X INSTR(X,'2272')
2272 1
22722 1
But then, if they always contain numbers, why are you storing them as strings?!
If you can have letters in your columns and you still want the numbers sorted numerically, take a look at:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9522557800346940016
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment