Bitmap Index vs. B-tree Index: Which and When?

最新推荐文章于 2026-01-07 10:43:04 发布
原创 最新推荐文章于 2026-01-07 10:43:04 发布 · 523 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#random #table #sql #statistics #access #disk

本文通过实验对比了Bitmap Index和B-tree Index在不同数据分布情况下的性能表现，揭示了两种索引类型的适用场景。

Bitmap Index vs. B-tree Index: Which and When?
by Vivek Sharma

Understanding the proper application of each index can have a big impact on performance.

Conventional wisdom holds that bitmap indexes are most appropriate for columns having low distinct values—such as GENDER, MARITAL_STATUS, and RELATION. This assumption is not completely accurate, however. In reality, a bitmap index is always advisable for systems in which data is not frequently updated by many concurrent systems. In fact, as I'll demonstrate here, a bitmap index on a column with 100-percent unique values (a column candidate for primary key) is as efficient as a B-tree index.

In this article I'll provide some examples, along with optimizer decisions, that are common for both types of indexes on a low-cardinality column as well as a high-cardinality one. These examples will help DBAs understand that the usage of bitmap indexes is not in fact cardinality dependent but rather application dependent.

Comparing the Indexes

There are several disadvantages to using a bitmap index on a unique column—one being the need for sufficient space (and Oracle does not recommend it). However, the size of the bitmap index depends on the cardinality of the column on which it is created as well as the data distribution. Consequently, a bitmap index on the GENDER column will be smaller than a B-tree index on the same column. In contrast, a bitmap index on EMPNO (a candidate for primary key) will be much larger than a B-tree index on this column. But because fewer users access decision-support systems (DSS) systems than would access transaction-processing (OLTP) ones, resources are not a problem for these applications.

To illustrate this point, I created two tables, TEST_NORMAL and TEST_RANDOM. I inserted one million rows into the TEST_NORMAL table using a PL/SQL block, and then inserted these rows into the TEST_RANDOM table in random order: 

Create table test_normal (empno number(10), ename varchar2(30), sal number(10));

Begin
For i in 1..1000000
Loop
   Insert into test_normal 
   values(i, dbms_random.string('U',30), dbms_random.value(1000,7000));
   If mod(i, 10000) = 0 then
   Commit;
  End if;
End loop;
End;
/
  
Create table test_random 
as 
select /*+ append */ * from test_normal order by dbms_random.random;

SQL> select count(*) "Total Rows" from test_normal;

Total Rows
----------
   1000000

Elapsed: 00:00:01.09

SQL> select count(distinct empno) "Distinct Values" from test_normal;

Distinct Values
---------------
        1000000

Elapsed: 00:00:06.09
SQL> select count(*) "Total Rows" from test_random;

Total Rows
----------
   1000000

Elapsed: 00:00:03.05
SQL> select count(distinct empno) "Distinct Values" from test_random;

Distinct Values
---------------
        1000000

Elapsed: 00:00:12.07

Note that the TEST_NORMAL table is organized and that the TEST_RANDOM table is randomly created and hence has disorganized data. In the above table, column EMPNO has 100-percent distinct values and is a good candidate to become a primary key. If you define this column as a primary key, you will create a B-tree index and not a bitmap index because Oracle does not support bitmap primary key indexes.

To analyze the behavior of these indexes, we will perform the following steps:

  1. On TEST_NORMAL:
    1. Create a bitmap index on the EMPNO column and execute some queries with equality predicates.
    2. Create a B-tree index on the EMPNO column, execute some queries with equality predicates, and compare the logical and physical I/Os done by the queries to fetch the results for different sets of values.
  2. On TEST_RANDOM:
    1. Same as Step 1A.
    2. Same as Step 1B.
  3. On TEST_NORMAL:
    1. Same as Step 1A, except that the queries are executed within a range of predicates.
    2. Same as Step 1B, except that the queries are executed within a range of predicates. Now compare the statistics.
  4. On TEST_RANDOM:
    1. Same as Step 3A.
    2. Same as Step 3B.
  5. On TEST_NORMAL:
    1. Create a bitmap index on the SAL column, and then execute some queries with equality predicates and some with range predicates.
    2. Create a B-tree index on the SAL column, and then execute some queries with equality predicates and some with range predicates (same set of values as in Step 5A). Compare the I/Os done by the queries to fetch the results.
  6. Add a GENDER column to both of the tables, and update the column with three possible values: M for male, F for female, and null for N/A. This column is updated with these values based on some condition.
  7. Create a bitmap index on this column, and then execute some queries with equality predicates.
  8. Create a B-tree index on the GENDER column, and then execute some queries with equality predicates. Compare to results from Step 7.

Steps 1 to 4 involve a high-cardinality (100-percent distinct) column, Step 5 a normal-cardinality column, and Steps 7 and 8 a low-cardinality column.

Step 1A (on TEST_NORMAL)

In this step, we will create a bitmap index on the TEST_NORMAL table and then check for the size of this index, its clustering factor, and the size of the table. Then we will run some queries with equality predicates and note the I/Os of these queries using this bitmap index. 

SQL> create bitmap index normal_empno_bmx on test_normal(empno);

Index created.

Elapsed: 00:00:29.06
SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;


Table analyzed.

Elapsed: 00:00:19.01
SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  2  from user_segments
  3* where segment_name in ('TEST_NORMAL','NORMAL_EMPNO_BMX');
 
SEGMENT_NAME                               Size in MB
------------------------------------       ---------------
TEST_NORMAL                                50
NORMAL_EMPNO_BMX                           28

Elapsed: 00:00:02.00
SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME                             CLUSTERING_FACTOR
------------------------------         ---------------------------------
NORMAL_EMPNO_BMX                       1000000

Elapsed: 00:00:00.00

You can see in the preceding table that the size of the index is 28MB and that the clustering factor is equal to the number of rows in the table. Now let's execute the queries with equality predicates for different sets of values: 

SQL> set autotrace only
SQL> select * from test_normal where empno=&empno;
Enter value for empno: 1000
old   1: select * from test_normal where empno=&empno
new   1: select * from test_normal where empno=1000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=4 Car
          d=1 Bytes=34)
   2    1     BITMAP CONVERSION (TO ROWIDS)
   3    2       BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_EMPNO_BMX'

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Step 1B (on TEST_NORMAL)

Now we will drop this bitmap index and create a B-tree index on the EMPNO column. As before, we will check for the size of the index and its clustering factor and execute the same queries for the same set of values, to compare the I/Os. 

SQL> drop index NORMAL_EMPNO_BMX;

Index dropped.

SQL> create index normal_empno_idx on test_normal(empno);

Index created.

SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  2  from user_segments
  3  where segment_name in ('TEST_NORMAL','NORMAL_EMPNO_IDX');

SEGMENT_NAME                               Size in MB
----------------------------------         ---------------
TEST_NORMAL                                50
NORMAL_EMPNO_IDX                           18

SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME                            CLUSTERING_FACTOR
----------------------------------    ----------------------------------
NORMAL_EMPNO_IDX                      6210

It is clear in this table that the B-tree index is smaller than the bitmap index on the EMPNO column. The clustering factor of the B-tree index is much nearer to the number of blocks in a table; for that reason, the B-tree index is efficient for range predicate queries.

Now we'll run the same queries for the same set of values, using our B-tree index. 

SQL> set autot trace
SQL> select * from test_normal where empno=&empno;
Enter value for empno: 1000
old   1: select * from test_normal where empno=&empno
new   1: select * from test_normal where empno=1000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=4 Car
          d=1 Bytes=34)
   2    1     INDEX (RANGE SCAN) OF 'NORMAL_EMPNO_IDX' (NON-UNIQUE) (C
          ost=3 Card=1)

Statistics
----------------------------------------------------------
         29  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

As you can see, when the queries are executed for different set of values, the number of consistent gets and physical reads are identical for bitmap and B-tree indexes on a 100-percent unique column.

BITMAPEMPNOB-TREE
Consistent ReadsPhysical ReadsConsistent ReadsPhysical Reads
50100050
52239852
52854552
529800852
528534252
5212844452
5285852

Step 2A (on TEST_RANDOM)

Now we'll perform the same experiment on TEST_RANDOM: 

SQL> create bitmap index random_empno_bmx on test_random(empno);

Index created.

SQL> analyze table test_random compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  2  from user_segments
  3* where segment_name in ('TEST_RANDOM','RANDOM_EMPNO_BMX');
 
SEGMENT_NAME                               Size in MB
------------------------------------       ---------------
TEST_RANDOM                                50
RANDOM_EMPNO_BMX                           28

SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME                             CLUSTERING_FACTOR
------------------------------         ---------------------------------
RANDOM_EMPNO_BMX                       1000000

Again, the statistics (size and clustering factor) are identical to those of the index on the TEST_NORMAL table:

SQL> select * from test_random where empno=&empno;
Enter value for empno: 1000
old   1: select * from test_random where empno=&empno
new   1: select * from test_random where empno=1000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_RANDOM' (Cost=4 Card=1 Bytes=34)
   2    1     BITMAP CONVERSION (TO ROWIDS)
   3    2       BITMAP INDEX (SINGLE VALUE) OF 'RANDOM_EMPNO_BMX'

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Step 2B (on TEST_RANDOM)

Now, as in Step 1B, we will drop the bitmap index and create a B-tree index on the EMPNO column. 

SQL> drop index RANDOM_EMPNO_BMX;

Index dropped.

SQL> create index random_empno_idx on test_random(empno);

Index created.

SQL> analyze table test_random compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  2  from user_segments
  3  where segment_name in ('TEST_RANDOM','RANDOM_EMPNO_IDX');

SEGMENT_NAME                               Size in MB
----------------------------------         ---------------
TEST_RANDOM                                50
RANDOM_EMPNO_IDX                           18

SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME                            CLUSTERING_FACTOR
----------------------------------    ----------------------------------
RANDOM_EMPNO_IDX                      999830

This table shows that the size of the index is equal to the size of this index on TEST_NORMAL table but the clustering factor is much nearer to the number of rows, which makes this index inefficient for range predicate queries (which we'll see in Step 4). This clustering factor will not affect the equality predicate queries because the rows have 100-percent distinct values and the number of rows per key is 1.

Now let's run the queries with equality predicates and the same set of values. 

SQL> select * from test_random where empno=&empno;
Enter value for empno: 1000
old   1: select * from test_random where empno=&empno
new   1: select * from test_random where empno=1000

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=34)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_RANDOM' (Cost=4 Card=1 Bytes=34)
   2    1     INDEX (RANGE SCAN) OF 'RANDOM_EMPNO_IDX' (NON-UNIQUE) (Cost=3 Card=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        515  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Again, the results are almost identical to those in Steps 1A and 1B. The data distribution did not affect the amount of consistent gets and physical reads for a unique column.

Step 3A (on TEST_NORMAL)

In this step, we will create the bitmap index (similar to Step 1A). We know the size and the clustering factor of the index, which equals the number of rows in the table. Now let's run some queries with range predicates. 

SQL> select * from test_normal where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old   1: select * from test_normal where empno between &range1 and &range2
new   1: select * from test_normal where empno between 1 and 2300

2300 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=451 Card=2299 Bytes=78166)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=451 Card=2299 Bytes=78166)
   2    1     BITMAP CONVERSION (TO ROWIDS)
   3    2       BITMAP INDEX (RANGE SCAN) OF 'NORMAL_EMPNO_BMX'

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        331  consistent gets
          0  physical reads
          0  redo size
     111416  bytes sent via SQL*Net to client
       2182  bytes received via SQL*Net from client
        155  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2300  rows processed

Step 3B (on TEST_NORMAL)

In this step, we'll execute the queries against the TEST_NORMAL table with a B-tree index on it. 

SQL> select * from test_normal where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old   1: select * from test_normal where empno between &range1 and &range2
new   1: select * from test_normal where empno between 1 and 2300

2300 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=2299 Bytes=78166)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=23 Card=2299 Bytes=78166)
   2    1     INDEX (RANGE SCAN) OF 'NORMAL_EMPNO_IDX' (NON-UNIQUE) (Cost=8 Card=2299)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        329  consistent gets
         15  physical reads
          0  redo size
     111416  bytes sent via SQL*Net to client
       2182  bytes received via SQL*Net from client
        155  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2300  rows processed

When these queries are executed for different sets of ranges, the results below show:

BITMAPEMPNO (Range)B-TREE
Consistent ReadsPhysical ReadsConsistent ReadsPhysical Reads
33101-23003290
28508-19802830
346191850-425034416
4273128888-3185042428
3712782900-8547836723
2157149984888-1000000213935

As you can see, the number of consistent gets and physical reads with both indexes is again nearly identical. The last range (984888-1000000) returned almost 15,000 rows, which was the maximum number of rows fetched for all the ranges given above. So when we asked for a full table scan (by giving the hint /*+ full(test_normal) */ ), the consistent read and physical read counts were 7,239 and 5,663, respectively.

Step 4A (on TEST_RANDOM)

 

In this step, we will run the queries with range predicates on the TEST_RANDOM table with bitmap index and check for consistent gets and physical reads. Here you'll see the impact of the clustering factor.

SQL>select * from test_random where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old   1: select * from test_random where empno between &range1 and &range2
new   1: select * from test_random where empno between 1 and 2300

2300 rows selected.

Elapsed: 00:00:08.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=453 Card=2299 Bytes=78166)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_RANDOM' (Cost=453 Card=2299 Bytes=78166)
   2    1     BITMAP CONVERSION (TO ROWIDS)
   3    2       BITMAP INDEX (RANGE SCAN) OF 'RANDOM_EMPNO_BMX'

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2463  consistent gets
       1200  physical reads
          0  redo size
     111416  bytes sent via SQL*Net to client
       2182  bytes received via SQL*Net from client
        155  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2300  rows processed

Step 4B (on TEST_RANDOM)

In this step, we will execute the range predicate queries on TEST_RANDOM with a B-tree index on it. Recall that the clustering factor of this index was very close to the number of rows in a table (and thus inefficient). Here's what the optimizer has to say about that: 

SQL> select * from test_random where empno between &range1 and &range2;
Enter value for range1: 1
Enter value for range2: 2300
old   1: select * from test_random where empno between &range1 and &range2
new   1: select * from test_random where empno between 1 and 2300

2300 rows selected.

Elapsed: 00:00:03.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=613 Card=2299 Bytes=78166)
   1    0   TABLE ACCESS (FULL) OF 'TEST_RANDOM' (Cost=613 Card=2299 Bytes=78166)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6415  consistent gets
       4910  physical reads
          0  redo size
     111416  bytes sent via SQL*Net to client
       2182  bytes received via SQL*Net from client
        155  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2300  rows processed

The optimizer opted for a full table scan rather than using the index because of the clustering factor:

BITMAPEMPNO (Range)B-TREE
Consistent ReadsPhysical ReadsConsistent ReadsPhysical Reads
246312001-230064154910
2114318-198063894910
257211351850-425064184909
3173162028888-3185064564909
2762135882900-8547864314909
72543329984888-100000072544909

For the last range (984888-1000000) only, the optimizer opted for a full table scan for the bitmap index, whereas for all ranges, it opted for a full table scan for the B-tree index. This disparity is due to the clustering factor: The optimizer does not consider the value of the clustering factor when generating execution plans using a bitmap index, whereas for a B-tree index, it does. In this scenario, the bitmap index performs more efficiently than the B-tree index.

The following steps reveal more interesting facts about these indexes.

Step 5A (on TEST_NORMAL)

Create a bitmap index on the SAL column of the TEST_NORMAL table. This column has normal cardinality. 

SQL> create bitmap index normal_sal_bmx on test_normal(sal);

Index created.

SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;

Table analyzed.

Now let's get the size of the index and the clustering factor. 

SQL>select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  2* from user_segments
  3* where segment_name in ('TEST_NORMAL','NORMAL_SAL_BMX');

SEGMENT_NAME                                Size in MB
------------------------------              --------------
TEST_NORMAL                                 50
NORMAL_SAL_BMX                              4

SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME                             CLUSTERING_FACTOR
------------------------------         ----------------------------------
NORMAL_SAL_BMX                         6001

Now for the queries. First run them with equality predicates:

SQL> set autot trace
SQL> select * from test_normal where sal=&sal;
Enter value for sal: 1869
old   1: select * from test_normal where sal=&sal
new   1: select * from test_normal where sal=1869

164 rows selected.

Elapsed: 00:00:00.08

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=168 Bytes=4032)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=39 Card=168 Bytes=4032)
   2    1     BITMAP CONVERSION (TO ROWIDS)
   3    2       BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        165  consistent gets
          0  physical reads
          0  redo size
       8461  bytes sent via SQL*Net to client
        609  bytes received via SQL*Net from client
         12  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        164  rows processed

and then with range predicates:

SQL> select * from test_normal where sal between &sal1 and &sal2;
Enter value for sal1: 1500
Enter value for sal2: 2000
old   1: select * from test_normal where sal between &sal1 and &sal2
new   1: select * from test_normal where sal between 1500 and 2000

83743 rows selected.

Elapsed: 00:00:05.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=83376 Bytes
          =2001024)
   1    0   TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=83376
          Bytes=2001024)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      11778  consistent gets
       5850  physical reads
          0  redo size
    4123553  bytes sent via SQL*Net to client
      61901  bytes received via SQL*Net from client
       5584  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      83743  rows processed

Now drop the bitmap index and create a B-tree index on TEST_NORMAL. 

SQL> create index normal_sal_idx on test_normal(sal);

Index created.

SQL> analyze table test_normal compute statistics for table for all indexes for all indexed columns;

Table analyzed.

Take a look at the size of the index and the clustering factor.

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  2  from user_segments
  3  where segment_name in ('TEST_NORMAL','NORMAL_SAL_IDX');

SEGMENT_NAME                         Size in MB
------------------------------       ---------------
TEST_NORMAL                          50
NORMAL_SAL_IDX                       17

SQL> select index_name, clustering_factor from user_indexes;

INDEX_NAME                           CLUSTERING_FACTOR
------------------------------       ----------------------------------
NORMAL_SAL_IDX                       986778

In the above table, you can see that this index is larger than the bitmap index on the same column. The clustering factor is also near the number of rows in this table.

Now for the tests; equality predicates first: 

SQL> set autot trace
SQL> select * from test_normal where sal=&sal;
Enter value for sal: 1869
old   1: select * from test_normal where sal=&sal
new   1: select * from test_normal where sal=1869

164 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=169 Card=168 Bytes=4032)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=169 Card=168 Bytes=4032)
   2    1     INDEX (RANGE SCAN) OF 'NORMAL_SAL_IDX' (NON-UNIQUE) (Cost=3 Card=168)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        177  consistent gets
          0  physical reads
          0  redo size
       8461  bytes sent via SQL*Net to client
        609  bytes received via SQL*Net from client
         12  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        164  rows processed

...and then, range predicates:

SQL> select * from test_normal where sal between &sal1 and &sal2;
Enter value for sal1: 1500
Enter value for sal2: 2000
old   1: select * from test_normal where sal between &sal1 and &sal2
new   1: select * from test_normal where sal between 1500 and 2000

83743 rows selected.

Elapsed: 00:00:04.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=83376 Bytes
          =2001024)
   1    0   TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=83376
          Bytes=2001024)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      11778  consistent gets
       3891  physical reads
          0  redo size
    4123553  bytes sent via SQL*Net to client
      61901  bytes received via SQL*Net from client
       5584  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      83743  rows processed

When the queries were executed for different set of values, the resulting output, as shown in the tables below, reveals that the numbers of consistent gets and physical reads are identical.

BITMAP
SAL (Equality)
B-TREERows Fetched
Consistent ReadsPhysical ReadsConsistent ReadsPhysical Reads
16501869177164 
1691633548181167 
1741666500187172 
756970008173 
1771632500190175 

BITMAP
SAL (Range)
B-TREERows Fetched
Consistent ReadsPhysical ReadsConsistent ReadsPhysical Reads
11778 5850 1500-2000 11778 3891 83743
11765 5468 2000-2500 11765 3879 83328
11753 5471 2500-3000 11753 3884 83318
17309 5472 3000-4000 17309 3892 166999
39398 5454 4000-7000 39398 3973 500520

For range predicates the optimizer opted for a full table scan for all the different set of values—it didn't use the indexes at all—whereas for equality predicates, the optimizer used the indexes. Again, the consistent gets and physical reads are identical.

Consequently, you can conclude that for a normal-cardinality column, the optimizer decisions for the two types of indexes were the same and there were no significant differences between the I/Os.

Step 6 (add a GENDER column)

Before performing the test on a low-cardinality column, let's add a GENDER column to this table and update it with M, F, and null values. 

SQL> alter table test_normal add GENDER varchar2(1);

Table altered.

SQL> select GENDER, count(*) from test_normal group by GENDER;

S     COUNT(*)
-     ----------
F     333769
M     499921
      166310

3 rows selected.

The size of the bitmap index on this column is around 570KB, as indicated in the table below: 

SQL> create bitmap index normal_GENDER_bmx on test_normal(GENDER);

Index created.

Elapsed: 00:00:02.08

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  2  from user_segments
  3  where segment_name in ('TEST_NORMAL','NORMAL_GENDER_BMX');

SEGMENT_NAME                        Size in MB
------------------------------      ---------------
TEST_NORMAL                         50
NORMAL_GENDER_BMX                   .5625

2 rows selected.

In contrast, the B-tree index on this column is 13MB in size, which is much bigger than the bitmap index on this column.

SQL> create index normal_GENDER_idx on test_normal(GENDER);

Index created.

SQL> select substr(segment_name,1,30) segment_name, bytes/1024/1024 "Size in MB"
  2  from user_segments
  3  where segment_name in ('TEST_NORMAL','NORMAL_GENDER_IDX');

SEGMENT_NAME                       Size in MB
------------------------------     ---------------
TEST_NORMAL                        50
NORMAL_GENDER_IDX                  13

2 rows selected.

Now, if we execute a query with equality predicates, the optimizer will not make use of this index, be it a bitmap or a B-tree. Rather, it will prefer a full table scan.

SQL> select * from test_normal where GENDER is null;

166310 rows selected.

Elapsed: 00:00:06.08

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=166310 Bytes=4157750)
   1    0   TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=166310 Bytes=4157750)

SQL> select * from test_normal where GENDER='M';

499921 rows selected.

Elapsed: 00:00:16.07

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=499921 Bytes=12498025)
   1    0   TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=499921Bytes=12498025)

SQL>select * from test_normal where GENDER='F'
 /

333769 rows selected.

Elapsed: 00:00:12.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=333769 Byte
          s=8344225)
   1    0   TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=333769
           Bytes=8344225)

Conclusions

Now that we understood how the optimizer reacts to these techniques, let's examine a scenario that clearly demonstrates the best respective applications of bitmap indexes and B-tree indexes.

With a bitmap index on the GENDER column in place, create another bitmap index on the SAL column and then execute some queries. The queries will be re-executed with B-tree indexes on these columns.

From the TEST_NORMAL table, you need the employee number of all the male employees whose monthly salaries equal any of the following values: 

1000
1500
2000
2500
3000
3500
4000
4500

Thus:

SQL>select * from test_normal 
where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';

This is a typical data warehouse query, which, of course, you should never execute on an OLTP system. Here are the results with the bitmap index in place on both columns:

SQL>select * from test_normal 
where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';

1453 rows selected.

Elapsed: 00:00:02.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=198 Card=754 Bytes=18850)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_NORMAL' (Cost=198 Card=754 Bytes=18850)
   2    1     BITMAP CONVERSION (TO ROWIDS)
   3    2       BITMAP AND
   4    3         BITMAP OR
   5    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
   6    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
   7    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
   8    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
   9    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
  10    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
  11    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
  12    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
  13    4           BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_SAL_BMX'
  14    3         BITMAP INDEX (SINGLE VALUE) OF 'NORMAL_GENDER_BMX'

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1353  consistent gets
        920  physical reads
          0  redo size
      75604  bytes sent via SQL*Net to client
       1555  bytes received via SQL*Net from client
         98  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1453  rows processed

And with the B-tree index in place:

SQL>select * from test_normal 
where sal in (1000,1500,2000,2500,3000,3500,4000,4500,5000) and GENDER='M';

1453 rows selected.

Elapsed: 00:00:03.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=601 Card=754 Bytes=18850)
   1    0   TABLE ACCESS (FULL) OF 'TEST_NORMAL' (Cost=601 Card=754 Bytes=18850)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       6333  consistent gets
       4412  physical reads
          0  redo size
      75604  bytes sent via SQL*Net to client
       1555  bytes received via SQL*Net from client
         98  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1453  rows processed

As you can see here, with the B-tree index, the optimizer opted for a full table scan, whereas in the case of the bitmap index, it used the index to answer the query. You can deduce performance by the number of I/Os required to fetch the result.

In summary, bitmap indexes are best suited for DSS regardless of cardinality for these reasons:

  • With bitmap indexes, the optimizer can efficiently answer queries that include AND, OR, or XOR. (Oracle supports dynamic B-tree-to-bitmap conversion, but it can be inefficient.)
  • With bitmaps, the optimizer can answer queries when searching or counting for nulls. Null values are also indexed in bitmap indexes (unlike B-tree indexes).
  • Most important, bitmap indexes in DSS systems support ad hoc queries, whereas B-tree indexes do not. More specifically, if you have a table with 50 columns and users frequently query on 10 of them—either the combination of all 10 columns or sometimes a single column—creating a B-tree index will be very difficult. If you create 10 bitmap indexes on all these columns, all the queries can be answered by these indexes, whether they are queries on all 10 columns, on 4 or 6 columns out of the 10, or on a single column. The AND_EQUAL hint provides this functionality for B-tree indexes, but no more than five indexes can be used by a query. This limit is not imposed with bitmap indexes.

In contrast, B-tree indexes are well suited for OLTP applications in which users' queries are relatively routine (and well tuned before deployment in production), as opposed to ad hoc queries, which are much less frequent and executed during nonpeak business hours. Because data is frequently updated in and deleted from OLTP applications, bitmap indexes can cause a serious locking problem in these situations.

The data here is fairly clear. Both indexes have a similar purpose: to return results as fast as possible. But your choice of which one to use should depend purely on the type of application, not on the level of cardinality.

 

Vivek Sharma (vivek.l.sharma@accenture.com or vlsharma@hotmail.com) is Senior Oracle DBA with Accenture India in Mumbai. He has six years of experience with Oracle technologies and is an Oracle Certified Professional. Vivek specializes in performance tuning and SQL-PL/SQL optimization.

xiaoshifeng
关注
6.S081-Lab 1: Xv6 and Unix utilities
MINGgoS的博客
03-17 904
更好的阅读体验 官方材料：https://pdos.csail.mit.edu/6.S081/2021/labs/util.html 参考资料： https://mit-public-courses-cn-translatio.gitbook.io/mit6-s081/ https://th0ar.gitbooks.io/xv6-chinese/content/ https://blog.miigon.net/posts/s081-ending/ https://wangchujia
Redis学习：redis.conf详解
FeiChangWuRao的博客
02-10 2449
Redis.conf配置文件是关于Redis重要属性的。配置好redis.conf,了解里面的属性很重要。 下面是我从github上直接拷贝的代码 # Redis configuration file example. # # Note that in order to read the configuration file, Redis must be # started with the file path as first argument: # # ./redis-server /path/to/r
服务的断路器：Hystrix
轻松的小希
01-31 5142
目录第一章 Hystrix介绍1.1、什么是Hystrix1.2、为啥用Hystrix第二章 Hystrix三大概念2.1、服务降级含义2.2、服务熔断含义2.3、服务限流含义第三章 Hystrix入门案例3.1、基础准备工作3.2、服务降级学习3.2.1、单个方法降级3.2.2、多个方法降级3.2.3、全局统一处理3.3、服务熔断学习3.3.1、服务熔断演示3.3.2、服务熔断类型3.3.3、服务熔断条件3.3.4、服务熔断设置3.4、服务限流学习第四章 Hystrix服务监控4.1、监控概述4.2、工程
Access to XMLHttpRequest at ‘http://xxxx‘ from origin ‘http://xxxx‘ has been blocked by CORS policy
热门推荐
笑三年-Dylen
10-30 11万+
解决CORS跨域问题 今天做前后端分离的项目时, 前端向后台发送请求发现报错: Access to XMLHttpRequest at ‘http://localhost:8082/doLogin’ from origin ‘http://localhost:8080’ has been blocked by CORS policy: Response to preflight request doesn’t pass access control check: No ‘Access-Control-A
JVM故障诊断调优
布道
08-21 6455
作为一名合格的架构师，如何能快速定位生产故障的根源，快速修复并恢复，已经成为其必备技能。以下是我整理的出想法： 目录 1.故障收集 2. 必备技能 2.1 网络与cup 2.2HotSpot VM 2.3 Mbean 2.4JVM工具 2.4.1jps 2.4.2jinfo 2.4.3jconsole 2.4.4jvisualvm 2.4.5jmap 2....
Druid “loopWaitCount 0, wait millis 6001” 报错
weixin_33998125的博客
04-12 1402
今天总结下自己使用Druid遇到的一个错误“loopWaitCount 0, wait millis 6001”报错信息：Causedby:org.springframework.jdbc.CannotGetJdbcConnectionException: CouldnotgetJDBCConnection;nestedexceptionisco...
Oracle数据库中B-Tree以及BitMap index 的性能对比
SunWuKong_Hadoop的博客
12-20 763
索引概述   什么是索引？   索引是Oracle数据库中提供的一种可选的数据结构，用于关联一个表。    为什么要使用索引？   索引在有些情况下可以加快访问速度，减少磁盘IO。   通常情况下时候使用索引？     表中的某列经常会在查询中使用，并且经常用返回占表中数据总量比例较少的row set。 引用完整性约束列。 unique key 。     下面我们来简...
Bitmap Index vs B-tree Index(原创)
czmmiao的专栏
03-08 242
Introduction Conventional wisdom holds that bitmap indexes are most appropriate for columns having low distinct values--such as GENDER, MARITAL_STATUS, and RELATION. This assumption is not complete...
052试题 174 / 175 - b-tree 索引, reverse index
文档搬运工
08-06 520
题目： 174. Which two statements are true regarding B-tree index? (Choose two.) A.The leaf blocks in the index are doubly linked. B.The leaf node stores a bitmap for each key value. C.The rows with NULL ...
ffmpeg command manual
小田的笔记簿
11-24 1780
ffmpeg 命令 文章目录ffmpeg 命令manualDESCRIPTIONDETAILED DESCRIPTIONFilteringStream copySTREAM SELECTIONDescriptionExamplesOPTIONSStream specifiersGeneric optionsAVOptionsMain optionsVideo optionsAdvanced Vid...
<!-- -*- nxml-child-indent: 4; tab-width: 4; indent-tabs-mode: nil -*- --> <config> <!-- For more detailed documentation on typical configuration options please see: https://sdk.collaboraonline.com/docs/installation/Configuration.html --> <!-- Note: 'default' attributes are used to document a setting's default value as well as to use as fallback. --> <!-- Note: When adding a new entry, a default must be set in WSD in case the entry is missing upon deployment. --> <accessibility desc="Accessibility settings"> <enable type="bool" desc="Controls whether accessibility support should be enabled or not." default="false">false</enable> </accessibility> <allowed_languages desc="List of supported languages of Writing Aids (spell checker, grammar checker, thesaurus, hyphenation) on this instance. Allowing too many has negative effect on startup performance." default="de_DE en_GB en_US es_ES fr_FR it nl pt_BR pt_PT ru">de_DE en_GB en_US es_ES fr_FR it nl pt_BR pt_PT ru</allowed_languages> <!-- These are the settings of external (remote) spellchecker and grammar checker services. Currently LanguageTool and Duden Korrekturserver APIs are supported, you can set either of them. By default they are disabled. To turn the support on, please set "enabled" property to true. It works with self hosted or cloud services, free and premium as well. The "base_url" may be https://api.languagetoolplus.com/v2 if the cloud version of LanguageTool is used. Please note that your data in the document e.g. the text part of it will be sent to the cloud API. Please read the respective privacy policies, e.g. https://languagetool.org/legal/privacy. --> <languagetool desc="Remote API settings for spell and grammar checking"> <enabled desc="Enable Remote Spell and Grammar Checker" type="bool" default="false">false</enabled> <base_url desc="HTTP endpoint for the API server, without /check or /languages postfix at the end." type="string" default=""></base_url> <user_name desc="LanguageTool or Duden account username for premium usage." type="string" default=""></user_name> <api_key desc="API key provided by LanguageTool or Duden account for premium usage." type="string" default=""></api_key> <ssl_verification desc="Enable or disable SSL verification. You may have to disable it in test environments with self-signed certificates." type="string" default="true">true</ssl_verification> <rest_protocol desc="REST API protocol. For LanguageTool leave it blank, for Duden Korrekturserver use the string 'duden'." type="string" default=""></rest_protocol> </languagetool> <deepl desc="DeepL API settings for translation service"> <enabled desc="If true, shows translate option as a menu entry in the compact view and as an icon in the tabbed view." type="bool" default="false">false</enabled> <api_url desc="URL for the API" type="string" default=""></api_url> <auth_key desc="Auth Key generated by your account" type="string" default=""></auth_key> </deepl> <sys_template_path desc="Path to a template tree with shared libraries etc to be used as source for chroot jails for child processes." type="path" relative="true" default="systemplate"></sys_template_path> <child_root_path desc="Path to the directory under which the chroot jails for the child processes will be created. Should be on the same file system as systemplate and lotemplate. Must be an empty directory." type="path" relative="true" default="jails"></child_root_path> <mount_jail_tree desc="Controls whether the systemplate and lotemplate contents are mounted or not, which is much faster than the default of linking/copying each file." type="bool" default="true">true</mount_jail_tree> <server_name desc="External hostname:port of the server running coolwsd. If empty, it's derived from the request (please set it if this doesn't work). May be specified when behind a reverse-proxy or when the hostname is not reachable directly." type="string" default=""></server_name> <file_server_root_path desc="Path to the directory that should be considered root for the file server. This should be the directory containing cool." type="path" relative="true" default="browser/../"></file_server_root_path> <hexify_embedded_urls desc="Enable to protect encoded URLs from getting decoded by intermediate hops. Particularly useful on Azure deployments" type="bool" default="false">false</hexify_embedded_urls> <experimental_features desc="Enable/Disable experimental features" type="bool" default="true">true</experimental_features> <memproportion desc="The maximum percentage of available memory consumed by all of the Collabora Online Development Edition processes, after which we start cleaning up idle documents. If cgroup memory limits are set, this is the maximum percentage of that limit to consume." type="double" default="80.0"></memproportion> <num_prespawn_children desc="Number of child processes to keep started in advance and waiting for new clients." type="uint" default="4">4</num_prespawn_children> <fetch_update_check desc="Every number of hours will fetch latest version data. Defaults to 10 hours." type="uint" default="10">10</fetch_update_check> <allow_update_popup desc="Allows notification about an update in the editor" type="bool" default="true">true</allow_update_popup> <per_document desc="Document-specific settings, including LO Core settings."> <max_concurrency desc="The maximum number of threads to use while processing a document." type="uint" default="4">4</max_concurrency> <batch_priority desc="A (lower) priority for use by batch eg. convert-to processes to avoid starving interactive ones" type="uint" default="5">5</batch_priority> <bgsave_priority desc="A (lower) priority for use by background save processes to free time for interactive ones" type="uint" default="5">5</bgsave_priority> <bgsave_timeout_secs desc="The default maximum number of seconds to wait for the background save processes to finish before giving up and reverting to synchronous saving" type="uint" default="120">120</bgsave_timeout_secs> <redlining_as_comments desc="If true show red-lines as comments" type="bool" default="false">false</redlining_as_comments> <pdf_resolution_dpi desc="The resolution, in DPI, used to render PDF documents as image. Memory consumption grows proportionally. Must be a positive value less than 385. Defaults to 96." type="uint" default="96">96</pdf_resolution_dpi> <idle_timeout_secs desc="The maximum number of seconds before unloading an idle document. Defaults to 1 hour." type="uint" default="3600">3600</idle_timeout_secs> <idlesave_duration_secs desc="The number of idle seconds after which document, if modified, should be saved. Disabled when 0. Defaults to 30 seconds." type="uint" default="30">30</idlesave_duration_secs> <autosave_duration_secs desc="The number of seconds after which document, if modified, should be saved. Disabled when 0. Defaults to 5 minutes." type="uint" default="300">300</autosave_duration_secs> <background_autosave desc="Allow auto-saves to occur in a forked background process where possible." type="bool" default="true">true</background_autosave> <background_manualsave desc="Allow manual save to occur in a forked background process where possible" type="bool" default="true">true</background_manualsave> <always_save_on_exit desc="On exiting the last editor, always perform a save and upload if the document had been modified. This is to allow the storage to store the document, if it had skipped doing so, previously, as an optimization." type="bool" default="false">false</always_save_on_exit> <limit_virt_mem_mb desc="The maximum virtual memory allowed to each document process. 0 for unlimited." type="uint">0</limit_virt_mem_mb> <limit_stack_mem_kb desc="The maximum stack size allowed to each document process. 0 for unlimited." type="uint">8000</limit_stack_mem_kb> <limit_file_size_mb desc="The maximum file size allowed to each document process to write. 0 for unlimited." type="uint">0</limit_file_size_mb> <limit_num_open_files desc="The maximum number of files allowed to each document process to open. 0 for unlimited." type="uint">0</limit_num_open_files> <limit_load_secs desc="Maximum number of seconds to wait for a document load to succeed. 0 for unlimited." type="uint" default="100">100</limit_load_secs> <limit_store_failures desc="Maximum number of consecutive save-and-upload to storage failures when unloading the document. 0 for unlimited (not recommended)." type="uint" default="5">5</limit_store_failures> <limit_convert_secs desc="Maximum number of seconds to wait for a document conversion to succeed. 0 for unlimited." type="uint" default="100">100</limit_convert_secs> <min_time_between_saves_ms desc="Minimum number of milliseconds between saving the document on disk." type="uint" default="500">500</min_time_between_saves_ms> <min_time_between_uploads_ms desc="Minimum number of milliseconds between uploading the document to storage." type="uint" default="5000">5000</min_time_between_uploads_ms> <cleanup desc="Checks for resource consuming (bad) documents and kills associated kit process. A document is considered resource consuming (bad) if is in idle state for idle_time_secs period and memory usage passed limit_dirty_mem_mb or CPU usage passed limit_cpu_per" enable="true"> <cleanup_interval_ms desc="Interval between two checks" type="uint" default="10000">10000</cleanup_interval_ms> <bad_behavior_period_secs desc="Minimum time period for a document to be in bad state before associated kit process is killed. If in this period the condition for bad document is not met once then this period is reset" type="uint" default="60">60</bad_behavior_period_secs> <idle_time_secs desc="Minimum idle time for a document to be candidate for bad state" type="uint" default="300">300</idle_time_secs> <limit_dirty_mem_mb desc="Minimum memory usage for a document to be candidate for bad state" type="uint" default="3072">3072</limit_dirty_mem_mb> <limit_cpu_per desc="Minimum CPU usage for a document to be candidate for bad state" type="uint" default="85">85</limit_cpu_per> <lost_kit_grace_period_secs desc="The minimum grace period for a lost kit process (not referenced by coolwsd) to resolve its lost status before it is terminated. To disable the cleanup of lost kits use value 0" default="120">120</lost_kit_grace_period_secs> </cleanup> </per_document> <per_view desc="View-specific settings."> <out_of_focus_timeout_secs desc="The maximum number of seconds before dimming and stopping updates when the browser tab is no longer in focus. Defaults to 300 seconds." type="uint" default="300">300</out_of_focus_timeout_secs> <idle_timeout_secs desc="The maximum number of seconds before dimming and stopping updates when the user is no longer active (even if the browser is in focus). Defaults to 15 minutes." type="uint" default="900">900</idle_timeout_secs> <custom_os_info desc="Custom string shown as OS version in About dialog, get from system if empty." type="string" default=""></custom_os_info> <min_saved_message_timeout_secs type="uint" desc="The minimum number of seconds before the last modified message is being displayed." default="6">6</min_saved_message_timeout_secs> </per_view> <ver_suffix desc="Appended to etags to allow easy refresh of changed files during development" type="string" default=""></ver_suffix> <logging> <color type="bool">true</color> <!-- Note to developers: When you do "make run", the logging.level will be set on the coolwsd command line, so if you want to change it for your testing, do it in Makefile.am, not here. --> <level type="string" desc="Can be 0-8 (with the lowest numbers being the least verbose), or none (turns off logging), fatal, critical, error, warning, notice, information, debug, trace" default="warning">warning</level> <level_startup type="string" desc="As for level - but for the initial startup phase which is most problematic, logging reverts to level configured above when startup is complete" default="trace">trace</level_startup> <disabled_areas type="string" desc="High verbosity logging ie. info to trace are disable-able, comma separated: Generic, Pixel, Socket, WebSocket, Http, WebServer, Storage, WOPI, Admin, Javascript" default="Socket,WebSocket,Admin,Pixel">Socket,WebSocket,Admin,Pixel</disabled_areas> <most_verbose_level_settable_from_client type="string" desc="A loggingleveloverride message from the client can not set a more verbose log level than this" default="notice">notice</most_verbose_level_settable_from_client> <least_verbose_level_settable_from_client type="string" desc="A loggingleveloverride message from a client can not set a less verbose log level than this" default="fatal">fatal</least_verbose_level_settable_from_client> <protocol type="bool" desc="Enable minimal client-site JS protocol logging from the start">false</protocol> <!-- lokit_sal_log example: Log WebDAV-related messages, that is interesting for debugging Insert - Image operation: "+TIMESTAMP+INFO.ucb.ucp.webdav+WARN.ucb.ucp.webdav" See also: https://docs.libreoffice.org/sal/html/sal_log.html --> <lokit_sal_log type="string" desc="Fine tune log messages from LOKit. Default is to suppress log messages from LOKit." default="-INFO-WARN">-INFO-WARN</lokit_sal_log> <file enable="false"> <!-- If you use other path than /var/log and you run coolwsd from systemd, make sure that you enable that path in coolwsd.service (ReadWritePaths). Also the log file path must be writable by the 'cool' user. --> <property name="path" desc="Log file path.">/var/log/coolwsd.log</property> <property name="rotation" desc="Log file rotation strategy. See Poco FileChannel.">never</property> <property name="archive" desc="Append either timestamp or number to the archived log filename.">timestamp</property> <property name="compress" desc="Enable/disable log file compression.">true</property> <property name="purgeAge" desc="The maximum age of log files to preserve. See Poco FileChannel.">10 days</property> <property name="purgeCount" desc="The maximum number of log archives to preserve. Use 'none' to disable purging. See Poco FileChannel.">10</property> <property name="rotateOnOpen" desc="Enable/disable log file rotation on opening.">true</property> <property name="flush" desc="Enable/disable flushing after logging each line. May harm performance. Note that without flushing after each line, the log lines from the different processes will not appear in chronological order.">false</property> </file> <anonymize> <anonymize_user_data type="bool" desc="Enable to anonymize/obfuscate of user-data in logs. If default is true, it was forced at compile-time and cannot be disabled." default="false">false</anonymize_user_data> <anonymization_salt type="uint" desc="The salt used to anonymize/obfuscate user-data in logs. Use a secret 64-bit random number." default="82589933">82589933</anonymization_salt> </anonymize> <docstats type="bool" desc="Enable to see document handling information in logs." default="false">false</docstats> <userstats desc="Enable user stats. i.e: logs the details of a file and user" type="bool" default="false">false</userstats> <disable_server_audit type="bool" desc="Disabled server audit dialog and notification. Admin will no longer see warnings in the application user interface. This doesn't affect log file." default="false">false</disable_server_audit> </logging> <canvas_slideshow_enabled type="bool" desc="If true, WebGl presentation rendered on the client side is enabled, otherwise interactive SVG is used." default="true">true</canvas_slideshow_enabled> <logging_ui_cmd> <merge type="bool" desc="If true, repeated commands after each other will be merged into 1 line. If false, every command will be 1 new line." default="true">true</merge> <merge_display_end_time type="bool" desc="If true, the duration of the merged command will also be logged." default="false">true</merge_display_end_time> <file enable="false"> <!-- If you use other path than /var/log and you run coolwsd from systemd, make sure that you enable that path in coolwsd.service (ReadWritePaths). Also the log file path must be writable by the 'cool' user. --> <property name="path" desc="Log file path.">/var/log/coolwsd-ui-cmd.log</property> <property name="purgeCount" desc="The maximum number of log archives to preserve. Use 'none' to disable purging. See Poco FileChannel.">10</property> <property name="rotateOnOpen" desc="Enable/disable log file rotation on opening.">true</property> <property name="flush" desc="Enable/disable flushing after logging each line. May harm performance. Note that without flushing after each line, the log lines from the different processes will not appear in chronological order.">false</property> </file> </logging_ui_cmd> <!-- Note to developers: When you do "make run", the trace_event[@enable] will be set on the coolwsd command line, so if you want to change it for your testing, do it in Makefile.am, not here. --> <trace_event desc="The possibility to turn on generation of a Chrome Trace Event file" enable="false"> <path desc="Output path for the Trace Event file, to which they will be written if turned on at run-time" type="string" default="/var/log/coolwsd.trace.json">/var/log/coolwsd.trace.json</path> </trace_event> <browser_logging desc="Logging in the browser console" default="false">false</browser_logging> <trace desc="Dump commands and notifications for replay. When 'snapshot' is true, the source file is copied to the path first." enable="false"> <path desc="Output path to hold trace file and docs. Use '%' for timestamp to avoid overwriting. For example: /some/path/to/cooltrace-%.gz" compress="true" snapshot="false"></path> <filter> <message desc="Regex pattern of messages to exclude"></message> </filter> <outgoing> <record desc="Whether or not to record outgoing messages" default="false">false</record> </outgoing> </trace> <net desc="Network settings"> <!-- On systems where localhost resolves to IPv6 [::1] address first, when net.proto is all and net.listen is loopback, coolwsd unexpectedly listens on [::1] only. You need to change net.proto to IPv4, if you want to use 127.0.0.1. --> <proto type="string" default="all" desc="Protocol to use IPv4, IPv6 or all for both">all</proto> <listen type="string" default="any" desc="Listen address that coolwsd binds to. Can be 'any' or 'loopback'.">any</listen> <!-- this allows you to shift all of our URLs into a sub-path from https://my.com/browser/a123... to https://my.com/my/sub/path/browser/a123... --> <service_root type="path" default="" desc="Prefix the base URL for all the pages, websockets, etc. with this path. This includes the discovery URL."></service_root> <post_allow desc="Allow/deny client IP address for POST(REST)." allow="true"> <host desc="The IPv4 private 192.168 block as plain IPv4 dotted decimal addresses.">192\.168\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="Ditto, but as IPv4-mapped IPv6 addresses">::ffff:192\.168\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="The IPv4 loopback (localhost) address.">127\.0\.0\.1</host> <host desc="Ditto, but as IPv4-mapped IPv6 address">::ffff:127\.0\.0\.1</host> <host desc="The IPv6 loopback (localhost) address.">::1</host> <host desc="The IPv4 private 172.16.0.0/12 subnet part 1.">172\.1[6789]\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="Ditto, but as IPv4-mapped IPv6 addresses">::ffff:172\.1[6789]\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="The IPv4 private 172.16.0.0/12 subnet part 2.">172\.2[0-9]\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="Ditto, but as IPv4-mapped IPv6 addresses">::ffff:172\.2[0-9]\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="The IPv4 private 172.16.0.0/12 subnet part 3.">172\.3[01]\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="Ditto, but as IPv4-mapped IPv6 addresses">::ffff:172\.3[01]\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="The IPv4 private 10.0.0.0/8 subnet (Podman).">10\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="Ditto, but as IPv4-mapped IPv6 addresses">::ffff:10\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}</host> </post_allow> <lok_allow desc="Allowed hosts as an external data source inside edited files. All allowed post_allow.host and storage.wopi entries are also considered to be allowed as a data source. Used for example in: PostMessage Action_InsertGraphic, =WEBSERVICE() function, external reference in the cell."> <host desc="The IPv4 private 192.168 block as plain IPv4 dotted decimal addresses.">192\.168\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="Ditto, but as IPv4-mapped IPv6 addresses">::ffff:192\.168\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="The IPv4 loopback (localhost) address.">127\.0\.0\.1</host> <host desc="Ditto, but as IPv4-mapped IPv6 address">::ffff:127\.0\.0\.1</host> <host desc="The IPv6 loopback (localhost) address.">::1</host> <host desc="The IPv4 private 172.16.0.0/12 subnet part 1.">172\.1[6789]\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="Ditto, but as IPv4-mapped IPv6 addresses">::ffff:172\.1[6789]\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="The IPv4 private 172.16.0.0/12 subnet part 2.">172\.2[0-9]\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="Ditto, but as IPv4-mapped IPv6 addresses">::ffff:172\.2[0-9]\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="The IPv4 private 172.16.0.0/12 subnet part 3.">172\.3[01]\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="Ditto, but as IPv4-mapped IPv6 addresses">::ffff:172\.3[01]\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="The IPv4 private 10.0.0.0/8 subnet (Podman).">10\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="Ditto, but as IPv4-mapped IPv6 addresses">::ffff:10\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}</host> <host desc="Localhost access by name">localhost</host> </lok_allow> <content_security_policy desc="Customize the CSP header by specifying one or more policy-directive, separated by semicolons. See w3.org/TR/CSP2"> </content_security_policy> <frame_ancestors> http://192.168.2.107:8881 http://10.1.200.64:* http://192.168.11.33:* </frame_ancestors> <connection_timeout_secs desc="Specifies the connection, send, recv timeout in seconds for connections initiated by coolwsd (such as WOPI connections)." type="int" default="30">30</connection_timeout_secs> <!-- this setting radically changes how online works, it should not be used in a production environment --> <proxy_prefix type="bool" default="false" desc="Enable a ProxyPrefix to be passed-in through which to redirect requests">false</proxy_prefix> </net> <ssl desc="SSL settings"> <!-- switches from https:// + wss:// to http:// + ws:// --> <enable type="bool" desc="Controls whether SSL encryption between coolwsd and the network is enabled (do not disable for production deployment). If default is false, must first be compiled with SSL support to enable." default="true">true</enable> <!-- SSL off-load can be done in a proxy, if so disable SSL, and enable termination below in production --> <termination desc="Connection via proxy where coolwsd acts as working via https, but actually uses http." type="bool" default="false">false</termination> <cert_file_path desc="Path to the cert file" type="path" relative="false">/etc/coolwsd/cert.pem</cert_file_path> <key_file_path desc="Path to the key file" type="path" relative="false">/etc/coolwsd/key.pem</key_file_path> <ca_file_path desc="Path to the ca file" type="path" relative="false">/etc/coolwsd/ca-chain.cert.pem</ca_file_path> <ssl_verification desc="Enable or disable SSL verification of hosts remote to coolwsd. If true SSL verification will be strict, otherwise certs of hosts will not be verified. You may have to disable it in test environments with self-signed certificates." type="string" default="false">false</ssl_verification> <cipher_list desc="List of OpenSSL ciphers to accept" type="string" default="ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH"></cipher_list> <hpkp desc="Enable HTTP Public key pinning" enable="false" report_only="false"> <max_age desc="HPKP's max-age directive - time in seconds browser should remember the pins" enable="true" type="uint" default="1000">1000</max_age> <report_uri desc="HPKP's report-uri directive - pin validation failure are reported at this URL" enable="false" type="string"></report_uri> <pins desc="Base64 encoded SPKI fingerprints of keys to be pinned"> <pin></pin> </pins> </hpkp> <sts desc="Strict-Transport-Security settings, per rfc6797. Subdomains are always included."> <enabled desc="Whether or not Strict-Transport-Security is enabled. Enable only when ready for production. Cannot be disabled without resetting the browsers." type="bool" default="false">false</enabled> <max_age desc="Strict-Transport-Security max-age directive, in seconds. 0 is allowed; please see rfc6797 for details. Defaults to 1 year." type="int" default="31536000">31536000</max_age> </sts> </ssl> <security desc="Altering these defaults potentially opens you to significant risk"> <seccomp desc="Should failure to enable seccomp system call filtering be a fatal error." type="bool" default="true">true</seccomp> <!-- deprecated: If capabilities is 'false', coolwsd will assume mount_namespaces of 'true' to achieve this goal, only avoiding chroot for process isolation if linux namespaces are unavailable --> <capabilities desc="Should we require capabilities to isolate processes into chroot jails" type="bool" default="true">true</capabilities> <jwt_expiry_secs desc="Time in seconds before the Admin Console's JWT token expires" type="int" default="1800">1800</jwt_expiry_secs> <enable_macros_execution desc="Specifies whether the macro execution is enabled in general. This will enable Basic and Python scripts to execute both installed and from documents. If it is set to false, the macro_security_level is ignored. If it is set to true, the mentioned entry specified the level of macro security." type="bool" default="false">false</enable_macros_execution> <macro_security_level desc="Level of Macro security. 1 (Medium) Confirmation required before executing macros from untrusted sources. 0 (Low, not recommended) All macros will be executed without confirmation." type="int" default="1">1</macro_security_level> <enable_websocket_urp desc="Should we enable URP (UNO remote protocol) communication over the websocket. This allows full control of the Kit child server to anyone with access to the websocket including executing macros without confirmation or running arbitrary shell commands in the jail." type="bool" default="false">false</enable_websocket_urp> <enable_metrics_unauthenticated desc="When enabled, the /cool/getMetrics endpoint will not require authentication." type="bool" default="false">false</enable_metrics_unauthenticated> <server_signature desc="Whether to send server signature in HTTP response headers" type="bool" default="false">false</server_signature> </security> <certificates> <database_path type="string" desc="Path to the NSS certificates that are available to all users" default=""></database_path> </certificates> <watermark> <opacity desc="Opacity of on-screen watermark from 0.0 to 1.0" type="double" default="0.2">0.2</opacity> <text desc="Watermark text to be displayed on the document if entered" type="string"></text> </watermark> <user_interface> <mode type="string" desc="Controls the user interface style. The 'default' means: Take the value from ui_defaults, or decide for one of compact or tabbed (default|compact|tabbed)" default="default">default</mode> <use_integration_theme desc="Use theme from the integrator" type="bool" default="true">true</use_integration_theme> <statusbar_save_indicator desc="Show saving status indicator in the statusbar" type="bool" default="true">true</statusbar_save_indicator> </user_interface> <storage desc="Backend storage"> <filesystem allow="false" /> <wopi desc="Allow/deny wopi storage." allow="true"> <max_file_size desc="Maximum document size in bytes to load. 0 for unlimited." type="uint">0</max_file_size> <locking desc="Locking settings"> <refresh desc="How frequently we should re-acquire a lock with the storage server, in seconds (default 15 mins) or 0 for no refresh" type="int" default="900">900</refresh> </locking> <alias_groups desc="default mode is 'first' it allows only the first host when groups are not defined. set mode to 'groups' and define group to allow multiple host and its aliases" mode="groups"> <group>192.168.2.107:8880,localhost:3000,10.1.200.64</group> </alias_groups> <is_legacy_server desc="Set to true for legacy server that need deprecated headers." type="bool" default="false">false</is_legacy_server> </wopi> <ssl desc="SSL settings"> <as_scheme type="bool" default="true" desc="When set we exclusively use the WOPI URI's scheme to enable SSL for storage">true</as_scheme> <enable type="bool" desc="If as_scheme is false or not set, this can be set to force SSL encryption between storage and coolwsd. When empty this defaults to following the ssl.enable setting"></enable> <cert_file_path desc="Path to the cert file. When empty this defaults to following the ssl.cert_file_path setting" type="path" relative="false"></cert_file_path> <key_file_path desc="Path to the key file. When empty this defaults to following the ssl.key_file_path setting" type="path" relative="false"></key_file_path> <ca_file_path desc="Path to the ca file. When empty this defaults to following the ssl.ca_file_path setting" type="path" relative="false"></ca_file_path> <cipher_list desc="List of OpenSSL ciphers to accept. If empty the defaults are used. These can be overridden only if absolutely needed."></cipher_list> </ssl> </storage> <admin_console desc="Web admin console settings."> <enable desc="Enable the admin console functionality" type="bool" default="true">true</enable> <enable_pam desc="Enable admin user authentication with PAM" type="bool" default="false">false</enable_pam> <username desc="The username of the admin console. Ignored if PAM is enabled."></username> <password desc="The password of the admin console. Deprecated on most platforms. Instead, use PAM or coolconfig to set up a secure password."></password> <logging desc="Log admin activities irrespective of logging.level"> <admin_login desc="log when an admin logged into the console" type="bool" default="true">true</admin_login> <metrics_fetch desc="log when metrics endpoint is accessed and metrics endpoint authentication is enabled" type="bool" default="true">true</metrics_fetch> <monitor_connect desc="log when external monitor gets connected" type="bool" default="true">true</monitor_connect> <admin_action desc="log when admin does some action for example killing a process" type="bool" default="true">true</admin_action> </logging> </admin_console> <monitors desc="Addresses of servers we connect to on start for monitoring"> <!-- <monitor desc="Address of the monitor and interval after which it should try reconnecting after disconnect" retryInterval="20">wss://foobar:234/ws</monitor> --> </monitors> <quarantine_files desc="Files are stored here to be examined later in cases of crashes or similar situation." default="false" enable="false"> <limit_dir_size_mb desc="Maximum directory size, in MBs. On exceeding the specified limit, older files will be deleted." default="250" type="uint">250</limit_dir_size_mb> <max_versions_to_maintain desc="How many versions of the same file to keep." default="5" type="uint">5</max_versions_to_maintain> <path desc="Absolute path of the directory under which quarantined files will be stored. Do not use a relative path." type="path" relative="false"></path> <expiry_min desc="Time in mins after quarantined files will be deleted." type="int" default="3000">3000</expiry_min> </quarantine_files> <cache_files desc="Files are cached here to speed up config support."> <path desc="Absolute path of the directory under which cached files will be stored. Do not use a relative path." type="path" relative="false"></path> <expiry_min desc="Time in mins after disuse at which cache files will be deleted." type="int" default="3000">1000</expiry_min> </cache_files> <extra_export_formats desc="Enable various extra export formats for additional compatibility. Note that disabling options here *only* disables them visually: these are all 'safe' to export, it might just be undesirable to show them, so you can't disable exporting these server-side"> <impress_swf desc="Enable exporting Adobe flash .swf files from presentations" type="bool" default="false">false</impress_swf> <impress_bmp desc="Enable exporting .bmp bitmap files from presentation slides" type="bool" default="false">false</impress_bmp> <impress_gif desc="Enable exporting .gif image files from presentation slides" type="bool" default="false">false</impress_gif> <impress_png desc="Enable exporting .png image files from presentation slides" type="bool" default="false">false</impress_png> <impress_svg desc="Enable exporting interactive .svg image files from presentations" type="bool" default="false">false</impress_svg> <impress_tiff desc="Enable exporting .tiff image files from presentation slides" type="bool" default="false">false</impress_tiff> </extra_export_formats> <serverside_config> <idle_timeout_secs desc="The maximum number of seconds before unloading an idle sub forkit. Defaults to 1 hour." type="uint" default="3600">3600</idle_timeout_secs> </serverside_config> <remote_config> <remote_url desc="remote server to which you will send request to get remote config in response" type="string" default=""></remote_url> </remote_config> <stop_on_config_change desc="Stop coolwsd whenever config files change." type="bool" default="false">false</stop_on_config_change> <remote_font_config> <url desc="URL of optional JSON file that lists fonts to be included in Online" type="string" default=""></url> </remote_font_config> <fonts_missing> <handling desc="How to handle fonts missing in a document: 'report', 'log', 'both', or 'ignore'" type="string" default="log">log</handling> </fonts_missing> <indirection_endpoint> <url desc="URL endpoint to server which servers routeToken in json format" type="string" default=""></url> <migration_timeout_secs desc="The maximum number of seconds waiting for shutdown migration message from indirection server before unloading an document. Defaults to 180 second." type="uint" default="180">180</migration_timeout_secs> <geolocation_setup> <enable desc="Enable geolocation_setup when using indirection server with geolocation configuration" type="bool" default="false">false</enable> <timezone desc="IANA timezone of server. For example: Europe/Berlin" type="string"></timezone> <allowed_websocket_origins desc="Origin header to get accepted during websocket upgrade"> <!-- <origin></origin> --> </allowed_websocket_origins> </geolocation_setup> <server_name desc="server name to show in cluster overview admin panel" type="string" default=""></server_name> </indirection_endpoint> <home_mode> <enable desc="Home users can enable this setting, which in turn disables welcome screen and user feedback popups, but also limits concurrent open connections to 20 and concurrent open documents to 10. The default means that number of concurrent open connections and concurrent open documents are unlimited, but welcome screen and user feedback cannot be switched off." type="bool" default="false">false</enable> </home_mode> <zotero desc="Zotero plugin configuration. For more details about Zotero visit https://www.zotero.org/"> <enable desc="Enable Zotero plugin." type="bool" default="true">true</enable> </zotero> <help_url desc="The Help root URL, or empty for no help (hides the Help buttons)" type="string" default="https://help.collaboraoffice.com/help.html?">https://help.collaboraoffice.com/help.html?</help_url> <overwrite_mode> <enable desc="Enable overwrite mode (user can use insert key)" type="bool" default="false">false</enable> </overwrite_mode> <wasm desc="WASM-specific settings"> <enable desc="Enable WASM support" type="bool" default="false">false</enable> <force desc="When enabled, all requests are redirected to WASM." type="bool" default="false">false</force> </wasm> <document_signing desc="Document signing settings"> <enable desc="Enable document signing" type="bool" default="true">true</enable> </document_signing> </config> 这是我的coolwsd.xml 这样可以吗
11-07
我们之前讨论的是Collabora Online的CSP配置问题，用户现在询问coolwsd.xml配置文件的可行性。...此外，引用[1]提到可以调整coolwsd.xml中的内存参数和超时设置。用户提供的配置片段中包含了frame_ancestors的设置，这...
最全user-agent，1000+条够你用
大蛇王的博客
07-12 2万+
User Agent中文名为用户代理，简称 UA，它是一个特殊字符串头，使得服务器能够识别客户使用的操作系统及版本、CPU 类型、浏览器及版本、浏览器渲染引擎、浏览器语言、浏览器插件等。 共计1456条 Mozilla/4.0 (compatible; GoogleToolbar 6.0.1411.1512; Windows XP 5.1; MSIE 6.0.2900.5512) Mozil...
PostgreSQL与MySQL选型对比
rchm8519的专栏
01-04 838
PostgreSQL与MySQL对比摘要：PostgreSQL适合复杂查询、高一致性及GIS应用，支持丰富数据类型和扩展功能；MySQL则在简单读写、云服务集成和主从复制上表现优异。选型关键因素包括：查询复杂度、数据模型、事务要求、团队经验和云服务需求。复杂业务推荐PostgreSQL，简单OLTP场景可选MySQL。混合架构可结合两者优势，最终应通过实际测试验证性能。建议不确定场景优先考虑PostgreSQL的功能超集特性。
PostgreSQL 中 nextval() 的线程安全性解析
Vic10101的博客
01-04 859
nextval()函数通过原子操作、内部锁机制和非事务性设计，确保了在并发环境下的线程安全性。这些机制使得nextval()在高并发场景下也能稳定地生成唯一的序列值，无需担心线程安全问题。同时，通过查询序列的元数据，我们可以在不改变序列当前状态的情况下，预览序列的下一个值。在 Java 应用程序中，无论是使用 JDBC 还是 ORM 框架，都可以方便地利用nextval()来生成唯一的序列值，从而确保数据的唯一性和一致性。希望本文能帮助你更好地理解和使用 PostgreSQL 的nextval()
关系型数据库、NoSQL、NewSQL 选型比对
最新发布
weixin_44821805的博客
01-07 206
垂直扩展为主（升级硬件），水平扩展需分库分表（Sharding-JDBC/MyCat），复杂度高。支持复杂 SQL（JOIN / 子查询 / 事务），OLTP/OLAP 均能覆盖（需优化）灵活 Schema（文档 / 键值 / 列族 / 图）、弱 JOIN。非结构化数据、高并发读写、灵活扩展（日志 / 缓存 / 物联网）分布式并发（TiDB 集群 TPS 可达百万），支持高并发事务。结构化数据、强事务、复杂关联（订单 / 支付 / 用户系统）海量结构化数据、强事务、高并发（互联网核心业务 / 金融）
Docker 部署 PostgreSQL 数据库教程
java_logo的专栏
01-05 969
PostgreSQL 是一款开源免费的高级关系型数据库管理系统，始于 1986 年，由全球开发者社区持续维护迭代，兼具悠久历史与前沿特性。它严格遵循 ACID 事务原则，确保数据读写的一致性与可靠性，同时突破传统关系型数据库局限，支持多模型存储——既能兼容标准 SQL，又可高效处理 JSON、数组、地理空间（GIS）等复杂数据类型。
PostgreSQL -- 开源对象-关系型数据库
TwoYellowGuns的博客
01-04 1082
PostgreSQL（通常简称为 Postgres）是一个功能强大的开源对象-关系型数据库系统，以其稳定性、标准兼容性、可扩展性和丰富的功能而广受开发者和企业的青睐。
优化示例：B-Tree与Bitmap索引对比与Oracle Index创建
本文主要关注 Oracle 的两种常见索引类型：B-Tree 索引和 Bitmap 索引，以及它们在实际优化中的应用。 B-Tree 索引是 Oracle 中最常用的索引类型，它采用平衡二叉树结构，适用于高基数（即不同值的数量）的列。B-...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值