Home » RDBMS Server » Performance Tuning » Number of values in Not in list (Oracle 11 g)
Number of values in Not in list [message #661312] Tue, 14 March 2017 06:29 Go to next message
saipradyumn
Messages: 311
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

I have query with not in condition which contains nearly 150 values in the list.All these values directly hard coded in the query itself.
We are using the same kind restriction for multiple times with different tables.
I want to create one temp table with those value and wants to restrict the data with the help of newly created table and not exist clause.

We have performance problem for that query.There are multiple reason for this performance problem.
I am thinking this also one of the reason.


Please suggest me.If we have more than 100 columns Can I go for creating a new table instead of hard coding.

Thanks
SaiPradyumn
Re: Number of values in Not in list [message #661313 is a reply to message #661312] Tue, 14 March 2017 06:39 Go to previous messageGo to next message
cookiemonster
Messages: 12961
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd have created the table for code maintenance reasons long ago. No idea what affect it'll have on performance, why don't you try it?
Re: Number of values in Not in list [message #661314 is a reply to message #661313] Tue, 14 March 2017 06:47 Go to previous messageGo to next message
saipradyumn
Messages: 311
Registered: October 2011
Location: Hyderabad
Senior Member
Anyway I am creating the table.Just for confirmation checking with you people(experts).
Re: Number of values in Not in list [message #661315 is a reply to message #661312] Tue, 14 March 2017 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I want to create one temp table with those value and wants to restrict the data with the help of newly created table and not exist clause.
Why do you think created a table on the fly with values will be more efficient than hard coded values?
Anyway, both are bad!

Re: Number of values in Not in list [message #661316 is a reply to message #661315] Tue, 14 March 2017 07:23 Go to previous messageGo to next message
cookiemonster
Messages: 12961
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not sure when you want a temp table for this, if the values are currently hard-coded then they don't change. You want a permanent table.
Re: Number of values in Not in list [message #661639 is a reply to message #661312] Sat, 25 March 2017 15:38 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
Storing List of items in the Database was a usual practice at my place of work.
We used Tables to hold Menus (Pick List), Individual Variables and Values, and
Variable IN List for Queries such as in this case. It made it much easier and
quicker to maintain data verses SQL Scripts/Program Code. Generally code changes
had to be scheduled with Programmers/DBA Staff, go through a testing cycle, then
scheduled for deployment to Production. In some cases we left the maintenance
of these tabes to the User Support Staff. Then they were responsible for the
the change implementation cycle.


Suggest considering a 2 table approach to store these "IN" List.
The 1st table "list_master" is used to identify the List and
assign a List Grouping ID. The "list_items" is the table holding the
items to be included into the "IN" List.

This is a sample of a 2 Table Design. The actual Columns for each Table
could vary on how you want to approach the solution.

CREATE TABLE list_master
( list_id           NUMBER       NOT NULL
 ,list_description  VARCHAR2(30) NOT NULL
 ,list_notes        VARCHAR2(500)
 ,CONSTRAINT list_master_pk PRIMARY KEY (list_id)
);


CREATE TABLE list_items
( list_item_id      NUMBER       NOT NULL
 ,list_id           NUMBER       NOT NULL
 ,list_item         VARCHAR2(20) NOT NULL
 ,CONSTRAINT  list_items_pk PRIMARY KEY (list_item_id)
 ,CONSTRAINT  list_items_list_master_fk
  FOREIGN KEY (list_id)
  REFERENCES  list_master(list_id)
);

CREATE INDEX list_items_ak
  ON list_items (list_id, list_item);


-- Sample Data
					
-- Master List
INSERT INTO list_master (list_id,list_description,list_notes) VALUES (10,'TEST NUMBER',NULL);
INSERT INTO list_master (list_id,list_description,list_notes) VALUES (20,'TEST CHARACTER',NULL);
INSERT INTO list_master (list_id,list_description,list_notes) VALUES (30,'TEST DATE',NULL);
COMMIT;

-- List Items
INSERT INTO list_items (list_item_id,list_id,list_item) VALUES (1,10,'20');
INSERT INTO list_items (list_item_id,list_id,list_item) VALUES (2,10,'30');
INSERT INTO list_items (list_item_id,list_id,list_item) VALUES (3,20,'FORD');
INSERT INTO list_items (list_item_id,list_id,list_item) VALUES (4,20,'SMITH');
INSERT INTO list_items (list_item_id,list_id,list_item) VALUES (5,30,'4/2/1981');
INSERT INTO list_items (list_item_id,list_id,list_item) VALUES (6,30,'11/17/1981');
COMMIT;


The following "IN" List Examples are based on the Oracle SCOTT User
with the EMP Table and Data. Also use the Sample Data listed above.

SCOTT.SQL can be found in the {Oracle Home Dir}\RDBMS\ADMIN\SCOTT.SQL

SQL>-- Setting Date Format for Session (Connect as Scott).
SQL>ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY';

Session altered.

SQL>
SQL>-- Number List Example
SQL>SELECT empno, deptno, ename, hiredate FROM emp
  2    WHERE deptno IN (SELECT TO_NUMBER(list_item) FROM list_items WHERE list_id = 10);

     EMPNO     DEPTNO ENAME      HIREDATE
---------- ---------- ---------- ----------
      7369         20 SMITH      12/17/1980
      7499         30 ALLEN      02/20/1981
      7521         30 WARD       02/22/1981
      7566         20 JONES      04/02/1981
      7654         30 MARTIN     09/28/1981
      7698         30 BLAKE      05/01/1981
      7788         20 SCOTT      04/19/1987
      7844         30 TURNER     09/08/1981
      7876         20 ADAMS      05/23/1987
      7900         30 JAMES      12/03/1981
      7902         20 FORD       12/03/1981

11 rows selected.

SQL>
SQL>-- Character List Example
SQL>SELECT empno, deptno, ename, hiredate FROM emp
  2    WHERE ename IN (SELECT list_item FROM list_items WHERE list_id = 20);

     EMPNO     DEPTNO ENAME      HIREDATE
---------- ---------- ---------- ----------
      7369         20 SMITH      12/17/1980
      7902         20 FORD       12/03/1981

SQL>
SQL>-- Date List Example
SQL>SELECT empno, deptno, ename, hiredate FROM emp
  2    WHERE hiredate IN (SELECT TO_DATE(list_item, 'mm/dd/yyyy') FROM list_items WHERE list_id = 30);

     EMPNO     DEPTNO ENAME      HIREDATE
---------- ---------- ---------- ----------
      7566         20 JONES      04/02/1981
      7839         10 KING       11/17/1981
Re: Number of values in Not in list [message #662292 is a reply to message #661639] Mon, 24 April 2017 01:20 Go to previous message
saipradyumn
Messages: 311
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Mikek,

Thanks for your explanation with example.As I am using same hard coded values multiple times, I had created a table which holds those values and restricted by using NON EXISTS condition

Thanks
SaiPradyumn
Previous Topic: Help Tuning slow query
Next Topic: How to identify index type for partitioned tables
Goto Forum:
  


Current Time: Fri Dec 15 17:00:40 CST 2017

Total time taken to generate the page: 0.07022 seconds