Re: SQL Query help

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 2 Jun 2015 08:08:29 +0100
Message-ID: <49875EB0D9C64CC492B85F6F963B67A1_at_Primary>

Giving us the table definitions it good.

To get any further, though, we need some information about the number of rows in each table, the number of rows identified individually (and then in combination ) by each predicates, any referential integrity constraints and details of all indexes, and the current execution plan.

Given that your query takes only 17 seconds the best view of the plan would come from a basic SQL*Plus session doing:

set serveroutput off
set linesize 255
set trimspool on
set pagesize 60
set arraysize 1000

alter session set statistics_level = all;

• run your query

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

alter session set statistics_level = typical;

This will give us the plan, AND the ability to compare Oracle's prediction of rows processed with the actuals; this is often sufficient to identify at least the initial problems with an execution plan.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

• Original Message ----- From: "Nik Tek" <niktek2005_at_gmail.com> To: "ORACLE-L" <oracle-l_at_freelists.org> Sent: Tuesday, June 02, 2015 2:08 AM Subject: SQL Query help

| Hi,
|
| Below is the tables structure, and the query is written as follows.
|
| CREATE TABLE PORT_MEMBERSHIP(
| PORT_KEY varchar2(50) NOT NULL,
| ID int NOT NULL,
| PORTGROUP_ID integer NOT NULL ,
| DOC_HOST_ID integer NOT NULL,
| LG_KEY varchar2(50) NULL,
| CONSTRAINT PK_PORT_MEMBERSHIP PRIMARY KEY (ID ASC,PORT_KEY ASC)
| );
|
|
| CREATE TABLE VP_PORT(
| ID integer NOT NULL,
| PORT_KEY varchar2(50) NOT NULL,
| VPPNAME varchar2(50) NULL,
| DESCRIP varchar2(80) NULL,
| CONFIG_VERSION integer NULL,
| BUNK_FLG integer(1) NULL,
| CONFLICT_FLG integer(1) NULL,
| CONNECTION_GATE integer NULL,
| STATUS_DETAIL varchar2(255) NULL,
| CONSTRAINT PK_VP_PORT PRIMARY KEY (ID ASC,PORT_KEY ASC)
| );
|
| CREATE TABLE GROUP_PORT(
| ID int NOT NULL,
| PORTGROUP_NAME varchar2(56) NOT NULL,
| DESCRIPTION varchar2(56) NULL,
| PORT_NAME_FORMAT varchar2(56) NULL,
| TRAFFIC_FILTER_OVERRIDE integer(1),
| SERIAL_PORT int NULL,
| PORTGROUP_FLG integer(1),
| POLICY_OVERRIDE integer(1),
| EXPANDED numeric(1, 0) ,
| SPAR_CONFIG_PORT int NULL,
| CONSTRAINT PK_VPX_DVPORTGROUP PRIMARY KEY (ID ASC)
| );
|
|
| --query
| select p1.PORTGROUP_ID,
| COUNT(p1.PORT_KEY) AS AVAIL_PORTS
| from PORT_MEMBERSHIP p1 left outer join VP_PORT p2
| on p1.ID = p2.ID and
| p1.PORT_KEY = p2.PORT_KEY
| where p1.DOC_HOST_ID = 0 and
| p2.PSERVICE is NULL and
| p1.PORTGROUP_ID IN
| (select ID
| from GROUP_PORT
| where EXPANDED = 1 and
| SERIAL_PORT > SPAR_CONFIG_PORT)
| group by p1.PORTGROUP_ID
| having (COUNT(p1.PORT_KEY) > 1561);
|
|
| Does this query look right, can this be optimized?
| It is now taking 17 secs.
|
|
| --
| Thank you
| NikTeki
|
|
|
| -----
| No virus found in this message.
| Checked by AVG - www.avg.com
| Version: 2015.0.5941 / Virus Database: 4354/9916 - Release Date: 06/01/15

---

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2015.0.5941 / Virus Database: 4354/9916 - Release Date: 06/01/15

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 02 2015 - 09:08:29 CEST