오라클 ROW 갯수 - olakeul ROW gaes-su

728x90

반응형

-모든 컬럼에 대한 row(행) 개수 구하기

SELECT COUNT(*) FROM 테이블명

-특정 컬럼에 대한 row(행) 개수 구하기

SELECT COUNT(컬럼명) FROM 테이블명

-특정 컬럼의 중복값을 제외한 row(행) 개수 구하기

SELECT COUNT(DISTINCT 컬럼명) FROM 테이블명

728x90

반응형

저작자표시

'Dev_Tip > Oracle_Tip' 카테고리의 다른 글

PREV 1 ··· 68 69 70 71 72 73 74 75 76 ··· 92 NEXT

2019. 7. 7. 12:36

728x90

반응형

WHERE 조건문에서 ROWNUM을 사용하여 원하는 개수만 조회할 수 있다

주의할점 : =이 아니고 <= 기호를 사용해야 한다

N개의 행만 조회하기

SELECT *

FROM 테이블이름

WHERE ROWNUM <= N

Example

WARNING_LOG 테이블의 데이터 조회 사진

WARNING_LOG 테이블의 2개의 행만 조회하는 사진

728x90

반응형

저작자표시

'DB > Oracle' 카테고리의 다른 글

Modify | Delete

NAME

PASSWORD

HOMEPAGE

SECRET

+ Recent posts

• 
  [Window] Batch File %~d0\%~⋯
• 
  [Spring] Session 우선순위⋯
• 
  [Window] 부팅시 프로그램 자⋯
• 
  [Tistory] 목록상단, 목록하⋯

Powered by Tistory, Designed by wallel

Rss Feed and Twitter, Facebook, Youtube, Google+

According to the documentation NUM_ROWS is the "Number of rows in the table", so I can see how this might be confusing. There, however, is a major difference between these two methods.

This query selects the number of rows in MY_TABLE from a system view. This is data that Oracle has previously collected and stored.

select num_rows from all_tables where table_name = 'MY_TABLE'

This query counts the current number of rows in MY_TABLE

select count(*) from my_table

By definition they are difference pieces of data. There are two additional pieces of information you need about NUM_ROWS.

1. In the documentation there's an asterisk by the column name, which leads to this note:

   Columns marked with an asterisk (*) are populated only if you collect statistics on the table with the ANALYZE statement or the DBMS_STATS package.

   This means that unless you have gathered statistics on the table then this column will not have any data.

2. Statistics gathered in 11g+ with the default estimate_percent, or with a 100% estimate, will return an accurate number for that point in time. But statistics gathered before 11g, or with a custom estimate_percent less than 100%, uses dynamic sampling and may be incorrect. If you gather 99.999% a single row may be missed, which in turn means that the answer you get is incorrect.

If your table is never updated then it is certainly possible to use ALL_TABLES.NUM_ROWS to find out the number of rows in a table. However, and it's a big however, if any process inserts or deletes rows from your table it will be at best a good approximation and depending on whether your database gathers statistics automatically could be horribly wrong.

Generally speaking, it is always better to actually count the number of rows in the table rather then relying on the system tables.