match against in mysql

CREATE TABLE `articles` (
  `id` int(11) NOT NULL DEFAULT '0',
  `title` varchar(65) DEFAULT NULL,
  `topic` varchar(25) NOT NULL DEFAULT '',
  `author` varchar(25) NOT NULL DEFAULT '',
  `ondate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `body` text NOT NULL,
  KEY `index_id` (`id`),
  FULLTEXT KEY `title` (`title`,`body`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8$$



select id,title FROM articles WHERE
    MATCH(title) AGAINST ('+cvs' IN BOOLEAN MODE) limit 1000;

what you have to consier is depending on DB Engine

'show processlist' in mysql

you can see process list

LOG setting in mysql

vi /etc/my.cnf

#general Query
log=mysql-general.log


#slow Query
slow-query-log = 1
long_query_time = 1
log_slow_queries = mysql-slow.log


restart mysql

show variables , show global variables

which my.cnf is read in mysql

mysql --help

count(*) over(order by no range between 2 preceding and 4 following)

select no, name, count(*)
over(order by no range between 2 preceding and 4 following) as sim_cnt
from pt_test

before -2 after 4 from your no


NO,NAME,SIM_CNT
-21,hello,1
1,hello2,4
4,hello2,3
4,hello,3
5,hello,3
10,hello,1

sum over() in oracle >> really good

select * from pt_test

NO,NAME
-21,hello
4,hello
1,hello2
4,hello2
5,hello
10,hello


select no, name, sum(no) over (partition by name ) value ,
sum(no) over (partition by name order by no range unbounded preceding) preceding from pt_test


NO,NAME,VALUE,PRECEDING
-21,hello,-2,-21
4,hello,-2,-17
5,hello,-2,-12
10,hello,-2,-2
1,hello2,5,1
4,hello2,5,5

rank, dense_rank, row_number difference

select * from pt_test



NO,NAME
-21,hello
4,hello
1,hello2
4,hello2
5,hello
10,hello

select no, name, rank() over(order by no desc) all_rank,
                 row_number() over(order by no desc) row_number,
                 dense_rank() over(order by no desc) dense_rank
from pt_test


NO,NAME,ALL_RANK,ROW_NUMBER,DENSE_RANK
10,hello,1,1,1
5,hello,2,2,2
4,hello,3,3,3
4,hello2,3,4,3
1,hello2,5,5,4
-21,hello,6,6,5

window function < rank() over >in oracle

select * from pt_test


NO,NAME
-21,hello
4,hello
1,hello2
4,hello2
5,hello
10,hello

select no, name, rank() over(order by no desc) all_rank,

 rank() over(partition by name  order by no desc) no_rank 

from pt_Test

NO,NAME,ALL_RANK,ALL_RANK_1

10,hello,1,1

5,hello,2,2

4,hello,3,3

4,hello2,3,1

1,hello2,5,2

-21,hello,6,4

How to use Connect by oracle

first create table


create table node(
em varchar(100),
ma varchar(100)
)


insert into node (em, ma) values(upper('a'), null)
insert into node (em, ma) values(upper('b'), upper('a'))
insert into node (em, ma) values(upper('c'), upper('a'))
insert into node (em, ma) values(upper('d'), upper('c'))
insert into node (em, ma) values(upper('e'), upper('c'))


commit;

select * from node




select level, lpad(' ', 4 * (level-1)) || em 사원, ma 관리자, connect_by_isleaf isleaf
from node
start with ma is null
connect by prior em = ma

select connect_by_root em 루트사원, sys_connect_by_path(em, '/') 경로, em, ma

from node

start with ma is null

connect by prior em = ma

Oracle Range Partition

// create partiton table

create table pt_test(
NO NUMBER NOT NULL,
NAME VARCHAR2(10) NULL
)
PARTITION BY RANGE(no)
(
PARTITION PT_DUMMY VALUES LESS THAN (-1)
);


//add partition
alter table PT_TEST ADD PARTITION PT_1 VALUES LESS THAN (5);

alter table PT_TEST ADD PARTITION PT_2 VALUES LESS THAN (11);

//TEST DATA INSERT
INSERT INTO PT_TEST VALUES (1 , 'A');
INSERT INTO PT_TEST VALUES (2 , 'B');
INSERT INTO PT_TEST VALUES (3 , 'C');
INSERT INTO PT_TEST VALUES (4 , 'D');
INSERT INTO PT_TEST VALUES (5 , 'E');
INSERT INTO PT_TEST VALUES (6 , 'F');
INSERT INTO PT_TEST VALUES (7 , 'G');
INSERT INTO PT_TEST VALUES (8 , 'H');
INSERT INTO PT_TEST VALUES (9 , 'I');
INSERT INTO PT_TEST VALUES (10 , 'J');
INSERT INTO PT_TEST VALUES (11 , 'K');



//Tset

SELECT * FROM PT_TEST
SELECT * FROM PT_TEST PARTITION (PT_1);
SELECT * FROM PT_TEST PARTITION (PT_2);



//drop partition

ALTER TABLE pt_test DROP PARTITION pt_1;



// change partition name

ALTER TABLE pt_test RENAME PARTITION pt_1 TO pt_one;



//  TRUNCATE partition data

ALTER TABLE pt_test TRUNCATE PARTITION pt_1;

how to analyze table index

analyze table document compute statistics

ex) DOCUMENT Table 만 Analyze

 

analyze index xpkdocbox compute statistics

ex) XPKDOCBOX Index 만 Analyze

select 'analyze table ' || table_name || ' estimate statistics;' from user_tables



select 'analyze index || index_name || estimate statistics;' from user_indexes

Oracle11g create Tablespace

First We Should Drop Tablespace if it exist

DROP TABLESPACE TODOLIST INCLUDING CONTENTS AND DATAFILES;

and Create 

CREATE TABLESPACE TODOLIST DATAFILE 

  '/oracle/11g/oradata/orcl/todolist.dbf' SIZE 100M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

NOLOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

free template webSite

http://designmodo.com/portfolio-website-templates/

oracle update table order by old_id

update (select * from mytable order by old_id) set new_id = mysequence.nextval;

TOP Coder

DB Connetion page

http://dev.anyframejava.org/docs/anyframe/4.1.0/reference/html/ch53.html