SQL Query help

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

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jun 02 2015 - 03:08:38 CEST