/
데이터 조회
데이터 조회
- Grey Shim
Owned by Grey Shim
Jan 17, 2022
Loading data...
Mach> SELECT * FROM mach_log; DEVICE TM TEMP ---------------------------------------------------------------- MSG ------------------------------------------------------------------------------------ 192.168.0.1 NULL NULL NULL 192.168.0.2 2014-06-15 19:50:03 484:382:010 82 error code = 20, critical warning 192.168.0.2 2014-06-15 19:50:03 484:382:008 57 error code = 20 192.168.0.1 2014-06-15 19:50:03 484:382:006 99 error code = 10, critical bug 192.168.0.1 2014-06-15 19:50:03 484:382:004 55 error code = 10 192.168.0.2 2014-06-15 19:50:03 484:382:002 31 normal state 192.168.0.1 2014-06-15 19:50:03 484:382:000 32 normal state [7] row(s) selected. Mach>
조건 절 조회
SELECT column_name,column_name FROM table_name WHERE column_name operator value;
Mach> SELECT * FROM mach_log WHERE device = '192.168.0.1'; DEVICE TM TEMP ---------------------------------------------------------------- MSG ------------------------------------------------------------------------------------ 192.168.0.1 NULL NULL NULL 192.168.0.1 2014-06-15 19:50:36 488:663:006 99 error code = 10, critical bug 192.168.0.1 2014-06-15 19:50:36 488:663:004 55 error code = 10 192.168.0.1 2014-06-15 19:50:36 488:663:000 32 normal state [4] row(s) selected. Mach> SELECT * FROM mach_log WHERE device = '192.168.0.1' AND temp > 30 AND temp < 50; DEVICE TM TEMP ---------------------------------------------------------------- MSG ------------------------------------------------------------------------------------ 192.168.0.1 2014-06-15 19:50:36 488:663:000 32 normal state [1] row(s) selected. Mach> SELECT * FROM mach_log where device > '192.168.0.1'; DEVICE TM TEMP ---------------------------------------------------------------- MSG ------------------------------------------------------------------------------------ 192.168.0.2 2014-06-15 19:50:36 488:663:010 82 error code = 20, critical warning 192.168.0.2 2014-06-15 19:50:36 488:663:008 57 error code = 20 192.168.0.2 2014-06-15 19:50:36 488:663:002 31 normal state [3] row(s) selected. Mach> SELECT * FROM mach_log WHERE msg LIKE '%error%'; DEVICE TM TEMP ---------------------------------------------------------------- MSG ------------------------------------------------------------------------------------ 192.168.0.2 2014-06-15 19:50:36 488:663:010 82 error code = 20, critical warning 192.168.0.2 2014-06-15 19:50:36 488:663:008 57 error code = 20 192.168.0.1 2014-06-15 19:50:36 488:663:006 99 error code = 10, critical bug 192.168.0.1 2014-06-15 19:50:36 488:663:004 55 error code = 10 [4] row(s) selected.
힌트(hint)를 이용한 검색 방향 지정하기
로그 테이블은 일반적으로 최근에 입력한 레코드부터 조회가 가능하다. 가장 먼저 입력한 레코드부터 조회하고 싶을 때에는 힌트를 이용해 조회 방향을 제어할 수 있다.
역방향 검색
기본값이며, /*+ SCAN_BACKWARD(table_name) */ 힌트를 추가하여 조회가 가능하다.
Mach> SELECT * FROM LOG; TIME ---------------------------------- 2021-01-04 00:00:00 000:000:000 2021-01-03 00:00:00 000:000:000 2021-01-02 00:00:00 000:000:000 2021-01-01 00:00:00 000:000:000 [4] row(s) selected. Elapsed time: 0.001 Mach> SELECT /*+ SCAN_BACKWARD(LOG) */ * FROM LOG; TIME ---------------------------------- 2021-01-04 00:00:00 000:000:000 2021-01-03 00:00:00 000:000:000 2021-01-02 00:00:00 000:000:000 2021-01-01 00:00:00 000:000:000 [4] row(s) selected. Elapsed time: 0.001
정방향 검색
/*+ SCAN_FORWARD(table_name) */ 힌트를 추가하여 조회가 가능하다.
Mach> SELECT /*+ SCAN_FORWARD(LOG) */ * FROM LOG; TIME ---------------------------------- 2021-01-01 00:00:00 000:000:000 2021-01-02 00:00:00 000:000:000 2021-01-03 00:00:00 000:000:000 2021-01-04 00:00:00 000:000:000 [4] row(s) selected. Elapsed time: 0.001
기본 스캔 방향 프로퍼티로 설정
TABLE_SCAN_DIRECTION 프로퍼티로 SELECT 문에 힌트가 없을 때 로그 테이블의 스캔 방향을 설정할 수 있다.