후훗 전에 게시판 테스트하느라 만든것이 있는데

9i로 만든거고요

편하게 하다보니 table full scan 해서 퍼포먼스가

별로 안좋아요.

데이타가 1~2만건 이하라면 써보셔도 괜찮으싶을듯

해서 올려봅니다.

너무 욕하진 마세요.

 

Create Table TB_CODE

( L_CODE varchar2(10) NOT NULL,

S_CODE varchar2(10) NOT NULL,

CD_NAME varchar2(60) NOT NULL,

USE_YN char(1) default 'Y',

ETC varchar2(100) default ' ',

Constraints TB_CODE_PK Primary Key (L_CODE,S_CODE) Using Index

Tablespace TOOLS

Storage(INITIAL 500K NEXT 500K PCTINCREASE 0)

)

Tablespace TOOLS

Storage(INITIAL 1M NEXT 1M PCTINCREASE 0)

/

 

Create Table TB_BOARD

( SEQ number(6) NOT NULL,

ID_NUM number(6) NOT NULL,

ID_DEP number(6) NOT NULL,

ID_REP number(6) default 0 NOT NULL,

L_CODE varchar2(10) NOT NULL,

S_CODE varchar2(10) NOT NULL,

TITLE varchar2(40) NOT NULL,

NAME varchar2(40) NOT NULL,

COMM varchar2(100) default ' ',

PWD varchar2(10) NULL,

READ number(6) default 0,

UP_DATE date default sysdate,

EMAIL varchar2(60) default ' ',

DEL_YN varchar2(60) default 'N',

Constraints TB_BOARD_PK Primary Key (SEQ) Using Index

Tablespace TOOLS

Storage(INITIAL 5M NEXT 5M PCTINCREASE 0)

)

Tablespace TOOLS

Storage(INITIAL 10M NEXT 10M PCTINCREASE 0)

/

 

Create Index TB_BOARD_ID on TB_BOARD(ID_NUM,ID_DEP,ID_REP)

Tablespace TOOLS

Storage(INITIAL 5M NEXT 5M PCTINCREASE 0);

 

Create Index TB_BOARD_CODE on TB_BOARD(L_CODE,S_CODE,ID_NUM,ID_DEP,ID_REP)

Tablespace TOOLS

Storage(INITIAL 5M NEXT 5M PCTINCREASE 0);

 

insert into tb_code

select '0','A','oracle','Y',' ' from dual union all

select '0','B','ms-sql','Y',' ' from dual union all

select '0','C','mysql','Y',' ' from dual union all

select 'A','1','DBA','Y',' ' from dual union all

select 'A','2','PLSQL','Y',' ' from dual union all

select 'A','3','OPS','Y',' ' from dual union all

select 'B','1','DBA','Y',' ' from dual union all

select 'B','2','TSQL','Y',' ' from dual union all

select 'B','3','DTS','Y',' ' from dual union all

select 'C','1','DBA','Y',' ' from dual union all

select 'C','2','SQL','Y',' ' from dual union all

select 'C','3','REPLICATION','Y',' ' from dual;

/

 

insert into tb_board

select 1,1,0,0,'A','1','오라클DBA1번','테스터1','...','111',0,sysdate,'a@a.net','N' from dual union all

select 2,1,1,1,'A','1','RE)오라클DBA1번','답변자1','...','111',0,sysdate,'a@a.net','N' from dual union all

select 3,2,1,1,'A','1','RE)오라클DBA1번','답변자2','...','111',0,sysdate,'a@a.net','N' from dual union all

select 4,3,1,1,'A','1','RE)오라클DBA1번','답변자3','...','111',0,sysdate,'a@a.net','N' from dual union all

select 5,1,2,2,'A','1','RE)RE)오라클DBA1번','답변자4','...','111',0,sysdate,'a@a.net','N' from dual union all

select 6,2,2,2,'A','1','RE)RE)오라클DBA1번','답변자5','...','111',0,sysdate,'a@a.net','N' from dual union all

select 7,1,2,4,'A','1','RE)RE)오라클DBA1번','답변자5','...','111',0,sysdate,'a@a.net','N' from dual union all

select 8,2,0,0,'A','1','오라클DBA2번','테스터2','...','111',0,sysdate,'a@a.net','N' from dual union all

select 9,1,1,8,'A','1','RE)오라클DBA2번','답변자1','...','111',0,sysdate,'a@a.net','N' from dual union all

select 10,2,1,8,'A','1','RE)오라클DBA2번','답변자2','...','111',0,sysdate,'a@a.net','N' from dual

/

 

Select ROW_NUMBER() OVER(ORDER BY ID_DEP desc,ID_NUM) RNUM,SEQ,ID_REP,ID_NUM,SUBSTR(LPAD(' '||LEVEL, 2*(LEVEL))||a.TITLE,1,30) "TITLE"

From TB_BOARD a, TB_CODE b

Where a.L_CODE = b.L_CODE

And a.S_CODE = b.S_CODE

And a.L_CODE = 'A'

And a.S_CODE = '1'

Start With a.ID_REP = 0

Connect By PRIOR a.SEQ = a.ID_REP

Order SIBLINGS by ID_NUM

/

 

이게 될려나...

쩝 위에 쿼리문이 틀렸군요

 

Select * from (

Select rownum rnum,SEQ,ID_REP,ID_NUM,SUBSTR(LPAD(' ', 2*(LEVEL))||a.TITLE,1,30) "TITLE"

From TB_CODE b,TB_BOARD a

Where a.L_CODE = b.L_CODE

And a.S_CODE = b.S_CODE

And b.L_CODE = 'A'

And b.S_CODE = '1'

And rownum < 10

Start With a.ID_REP = 0

Connect By PRIOR a.SEQ = a.ID_REP

Order SIBLINGS by decode(ID_REP,0,ID_NUM) desc,ID_NUM

)

where rnum > 4

/