NL with ambiguous order by - recreating index to flip result

View All Public Scripts Login to Run Script

Script Name NL with ambiguous order by - recreating index to flip result
Visibility Unlisted - anyone with the share link can access
Description code for ask-tom question. precondition is that the index rau_address_idx exists with asc to street. Should be run in parallel session while another session is running long running query with NL ( Statement 29 from https://livesql.oracle.com/apex/livesql/s/m9lxl8otep99ama1ggavsimzn)
Area SQL General
Contributor Ralph
Created Tuesday March 29, 2022

Statement 1

          SELECT * 
            FROM ( 
                 SELECT company_id, street, 
                        row_number() over ( partition by company_id order BY prio asc ) as row_num                        
                   FROM rau_address                      
                 ) 
           WHERE row_num = 1


COMPANY_ID STREET ROW_NUM
1 S1 1

COMPANY_ID	STREET	ROW_NUM
1	S1	1

Statement 2

DROP INDEX rau_address_idx

Index dropped.

Statement 3

CREATE INDEX rau_address_idx 
ON rau_address (COMPANY_ID, PRIO, STREET desc)

Index created.

Statement 4

          SELECT * 
            FROM ( 
                 SELECT company_id, street, 
                        row_number() over ( partition by company_id order BY prio asc ) as row_num                        
                   FROM rau_address                      
                 ) 
           WHERE row_num = 1


COMPANY_ID STREET ROW_NUM
1 S9999 1

COMPANY_ID	STREET	ROW_NUM
1	S9999	1

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database